SELECT count( DISTINCT glc, rdid ) FROM zhima_borrow_order_2023 where glc = 'T1C2S3O4F5T6' 转换成不使用 DISTINCT 的sql

可以使用GROUP BY来实现不使用DISTINCT的查询：

SELECT COUNT(*) FROM (SELECT rdid FROM zhima_borrow_order_2023 WHERE glc = 'T1C2S3O4F5T6' GROUP BY rdid) AS t;

这个查询会先根据条件`WHERE glc = 'T1C2S3O4F5T6'`过滤掉不符合条件的数据，然后再按照`rdid`进行分组，并统计分组后的数量。由于分组后的每组数据中，`glc`的值都相同，所以不需要对`glc`进行分组。最后，将分组后的结果作为子查询，再对子查询的结果进行数量统计即可。

相关问题

SELECT count(1) FROM (SELECT glc,rdid FROM zhima_borrow_order_2023 where 1 = 1 and libcode = 'STC001' GROUP BY glc, rdid ) as t 换种写法，要求不使用子查询和COUNT(DISTINCT)函数

可以使用以下方式进行重写：

SELECT COUNT(*) 
FROM (
  SELECT glc, rdid 
  FROM zhima_borrow_order_2023 
  WHERE 1 = 1 AND libcode = 'STC001' 
  GROUP BY glc, rdid 
  HAVING COUNT(*) > 0
) as t

这里使用了 HAVING 子句来过滤出现次数大于0的组合，然后再对结果进行计数。

select count(1)from (select distinct unique_id_station from d_wx_expansion_oarea2023 dweo where 1 = 1 and unique_id_station is not null and province_name ='河北省'and month_id ='202212' ) as a union all select count(1) from d_wx_expansion_oarea2023 where 1 = 1 and province_name ='河北省'and city_name ='邯郸市'and month_id ='202212' union all select count(1) from d_wx_expansion_oarea2023 where 1 = 1 and province_name ='河北省'and city_name ='邯郸市'and month_id ='202212' and is_pdcp_busy_week =1 union all select count(1)from(select distinct unique_id_station from d_wx_expansion_oarea2023 where 1 = 1 and province_name ='河北省' and city_name ='邯郸市'and month_id ='202212' and is_pdcp_busy_week =1 and unique_id_station is not null) as aaa将四个sql语句合成一个

SELECT
(SELECT COUNT(DISTINCT unique_id_station) FROM d_wx_expansion_oarea2023 WHERE province_name = '河北省' AND month_id = '202212' AND unique_id_station IS NOT NULL) AS cnt1,
(SELECT COUNT(*) FROM d_wx_expansion_oarea2023 WHERE province_name = '河北省' AND city_name = '邯郸市' AND month_id = '202212') AS cnt2,
(SELECT COUNT(*) FROM d_wx_expansion_oarea2023 WHERE province_name = '河北省' AND city_name = '邯郸市' AND month_id = '202212' AND is_pdcp_busy_week = 1) AS cnt3,
(SELECT COUNT(DISTINCT unique_id_station) FROM d_wx_expansion_oarea2023 WHERE province_name = '河北省' AND city_name = '邯郸市' AND month_id = '202212' AND is_pdcp_busy_week = 1 AND unique_id_station IS NOT NULL) AS cnt4;