select distinct 列名,count(*) from 表名 group by 列名 的结果如何导入另一个表中

Question

select * into mytable from ...

Answer 1

insert into 表 
select ........

Answer 2

由此各次查询的结构一样，可先对各次查询取并集（用Union） ，然后插入到表（select ... into ...）

Answer 3

加Distinct和不用Distinct有什么区别吗？

Answer 4

从给的查询语句看看，要筛选的列分组了，所以结果不会有重列。因此用不用distinc区别不大。

Answer 5

SELECT * INTO #TEMP FROM(
select quyu,sheng,hangyie,qte_bjmc,qte_mc ,count(*)[COUNT] from biyan002 group by quyu,sheng,hangyie,qte_bjmc,qte_mc
UNION
select quyu,sheng,hangyie,qtf_bjmc,qtf_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,qtf_bjmc,qtf_mc
UNION
select quyu,sheng,hangyie,ckj_gys,ckj_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,ckj_gys,ckj_mc
)T#TEMP可以改为你要的新表名，前提上面语句执行前不存在

Answer 6

完整的语句如下, SELECT * INTO NT1119 FROM  (
 select distinct quyu,sheng,hangyie,qta_bjmc,qta_mc ,count(*) [COUNT] from biyan002 group by quyu,sheng,hangyie,qta_bjmc,qta_mc
 UNION
select distinct quyu,sheng,hangyie,qtb_bjmc,qtb_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,qtb_bjmc,qtb_mc
 UNION
select distinct quyu,sheng,hangyie,qtc_bjmc,qtc_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,qtc_bjmc,qtc_mc
 UNION
select distinct quyu,sheng,hangyie,qtd_bjmc,qtd_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,qtd_bjmc,qtd_mc
 UNION
select distinct quyu,sheng,hangyie,qte_bjmc,qte_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,qte_bjmc,qte_mc
 UNION
select distinct quyu,sheng,hangyie,qtf_bjmc,qtf_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,qtf_bjmc,qtf_mc
 UNION
select distinct quyu,sheng,hangyie,ckj_gys,ckj_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,ckj_gys,ckj_mc 
 UNION
select distinct quyu,sheng,hangyie,t301a_gys,t301a_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,t301a_gys,t301a_mc
 UNION 
select distinct quyu,sheng,hangyie,zydy_gys,zydy_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,zydy_gys,zydy_mc
 UNION
select distinct quyu,sheng,hangyie,pc_dy_gys,pc_dy_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,pc_dy_gys,pc_dy_mc
 UNION
select distinct quyu,sheng,hangyie,nc_gys,nc_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,nc_gys,nc_mc
 UNION
select distinct quyu,sheng,hangyie,yp_gys,yp_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,yp_gys,yp_mc
 UNION
select distinct quyu,sheng,hangyie,zb_gys,zb_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,zb_gys,zb_mc
 UNION
select distinct quyu,sheng,hangyie,cpu_gys,cpu_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,cpu_gys,cpu_mc)
执行后,得到的提示如下:
消息 102，级别 15，状态 1，第 28 行
')' 附近有语法错误。
不太明白,( )是一对一了,哪里的语法错了?

Answer 7

select COALESCE(a.quyu, b.quyu, c.quyu) quyu, 
  COALESCE(a.sheng, b.sheng, c.sheng) sheng, 
  COALESCE(a.hangyie, b.hangyie, c.hangyie) hangyie,
  b.qte_bjmc, b.qte_mc, b.dte_count,
  c.qtf_bjmc, c.qtf_mc, c.qtf_count,
  d.ckj_gys, d.ckj_mc, d.ckj_count
from (select quyu,sheng,hangyie,qte_bjmc,qte_mc ,count(*) dte_count
    from biyan002 group by quyu,sheng,hangyie,qte_bjmc,qte_mc) a
  full join (select quyu,sheng,hangyie,qtf_bjmc,qtf_mc ,count(*) qtf_count
    from biyan002 group by quyu,sheng,hangyie,qtf_bjmc,qtf_mc) b
      on b.quyu=a.quyu and b.sheng=a.sheng and b.hangyie=a.hangyie
  full join (select quyu,sheng,hangyie,ckj_gys,ckj_mc ,count(*) ckj_count
    from biyan002 group by quyu,sheng,hangyie,ckj_gys,ckj_mc) c
      on c.quyu=a.quyu and c.sheng=a.sheng and c.hangyie=a.hangyie

Answer 8

更正select COALESCE(a.quyu, b.quyu, c.quyu) quyu, 
  COALESCE(a.sheng, b.sheng, c.sheng) sheng, 
  COALESCE(a.hangyie, b.hangyie, c.hangyie) hangyie,
  a.qte_bjmc, a.qte_mc, a.dte_count,
  b.qtf_bjmc, b.qtf_mc, b.qtf_count,
  c.ckj_gys, c.ckj_mc, c.ckj_count
from (select quyu,sheng,hangyie,qte_bjmc,qte_mc ,count(*) dte_count
    from biyan002 group by quyu,sheng,hangyie,qte_bjmc,qte_mc) a
  full join (select quyu,sheng,hangyie,qtf_bjmc,qtf_mc ,count(*) qtf_count
    from biyan002 group by quyu,sheng,hangyie,qtf_bjmc,qtf_mc) b
      on b.quyu=a.quyu and b.sheng=a.sheng and b.hangyie=a.hangyie
  full join (select quyu,sheng,hangyie,ckj_gys,ckj_mc ,count(*) ckj_count
    from biyan002 group by quyu,sheng,hangyie,ckj_gys,ckj_mc) c
      on c.quyu=a.quyu and c.sheng=a.sheng and c.hangyie=a.hangyie

Answer 9

楼主要的是不是这个效果？select COALESCE(a.quyu, b.quyu, c.quyu) quyu, 
  COALESCE(a.sheng, b.sheng, c.sheng) sheng, 
  COALESCE(a.hangyie, b.hangyie, c.hangyie) hangyie,
  a.dte_count, b.qtf_count, c.ckj_count
from (select quyu,sheng,hangyie,qte_bjmc,qte_mc ,count(*) dte_count
    from biyan002 group by quyu,sheng,hangyie,qte_bjmc,qte_mc) a
  full join (select quyu,sheng,hangyie,qtf_bjmc,qtf_mc ,count(*) qtf_count
    from biyan002 group by quyu,sheng,hangyie,qtf_bjmc,qtf_mc) b
      on b.quyu=a.quyu and b.sheng=a.sheng and b.hangyie=a.hangyie
  full join (select quyu,sheng,hangyie,ckj_gys,ckj_mc ,count(*) ckj_count
    from biyan002 group by quyu,sheng,hangyie,ckj_gys,ckj_mc) c
      on c.quyu=a.quyu and c.sheng=a.sheng and c.hangyie=a.hangyie

Answer 10

用Group by 就行了，distinct没意义

Answer 11

分析数据可以这样用SELECT  quyu
       ,sheng
       ,hangyie
       ,COUNT(distinct CHECKSUM(qte_bjmc,qte_mc)) AS [qte_bjmc|qte_mc]
       ,COUNT(distinct CHECKSUM(qtf_bjmc,qtf_mc)) AS [qtf_bjmc|qtf_mc]
       ,COUNT(distinct CHECKSUM(ckj_gys,ckj_mc)) AS [ckj_gys|ckj_mc ]
FROM    quyu
GROUP BY quyu
       ,sheng
       ,hangyie