好久没有用SQL统计数据了，今天接到一个临时任务，统计下药店的覆盖率，一通操作猛如虎，数据很快导出来了，但一细看，数据居然不对。写了10多年SQL ,这打击有点大；

--建表
CREATE TABLE [test](
	[sfmc] [varchar](100) NULL,
	[dsmc] [varchar](100) NULL,
	[xqmc] [varchar](100) NULL,
	[dwmc] [varchar](100) NULL,
	[sl] [int] default 0
) ON [PRIMARY]

　　插入测试数据

INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'金水区\', N\'AAA\', 0)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'金水区\', N\'BBB\', 0)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'金水区\', N\'CCC\', 0)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'二七区\', N\'ddd\', 0)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'二七区\', N\'EEE\', 0)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'金水区\', N\'AAA\', 1)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'金水区\', N\'AAA\', 2)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'金水区\', N\'BBB\', 2)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'金水区\', N\'BBB\', 1)
INSERT  [test] ([sfmc], [dsmc], [xqmc], [dwmc], [sl]) VALUES (N\'河南省\', N\'郑州市\', N\'金水区\', N\'BBB\', 5)

 

 select * from test

　　查询结果：

 

 查询需求：统计每个地区的单位(dwmc)数量,如 金水区有AAA BBB  CCC三家单位，数量应为3，格式如下图：

 

走的弯路是直接用count(*) 结合group by 操作，输出的结果当然不对；

SELECT SFMC,DSMC,xqmc,COUNT(*) FROM test GROUP BY SFMC,DSMC,xqmc

上述语句的结果

 

  

一番百度，更换以下SQL ，过到理想结果

--最终使用的SQL
SELECT SFMC,DSMC,xqmc,COUNT(*) FROM (SELECT DISTINCT SFMC,DSMC,XQMC,DWMC FROM TEST) AS A
GROUP BY A.SFMC,A.DSMC,A.xqmc

 

 

　　

 

版权声明：本文为lrzy原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/lrzy/p/13439510.html