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

nantian_service 2014-11-19 11:25:48
我写了以下的语句来统计表中相关列的数量:


select distinct quyu,sheng,hangyie,qte_bjmc,qte_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,qte_bjmc,qte_mc

select distinct quyu,sheng,hangyie,qtf_bjmc,qtf_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,qtf_bjmc,qtf_mc

select distinct quyu,sheng,hangyie,ckj_gys,ckj_mc ,count(*) from biyan002 group by quyu,sheng,hangyie,ckj_gys,ckj_mc


现在想将统计的结果加入到一个新表中再做分析. 该如何编写程序
...全文
688 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
还在加载中灬 2014-11-19
  • 打赏
  • 举报
 回复 
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可以改为你要的新表名，前提上面语句执行前不存在
freecodex 2014-11-19
  • 打赏
  • 举报
 回复
引用 4 楼 xxfvba 的回复:
加Distinct和不用Distinct有什么区别吗？
从给的查询语句看看，要筛选的列分组了，所以结果不会有重列。因此用不用distinc区别不大。
xxfvba 2014-11-19
  • 打赏
  • 举报
 回复
加Distinct和不用Distinct有什么区别吗？
freecodex 2014-11-19
  • 打赏
  • 举报
 回复
由此各次查询的结构一样，可先对各次查询取并集（用Union） ，然后插入到表（select ... into ...）
Yole 2014-11-19
  • 打赏
  • 举报
 回复 


insert into 表 
select ........
freecodex 2014-11-19
  • 打赏
  • 举报
 回复
select * into mytable from ...
在路上_- 2014-11-19
  • 打赏
  • 举报
 回复
学习了
引用 14 楼 roy_88 的回复:
分析数据可以这样用 
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
中国风 2014-11-19
  • 打赏
  • 举报
 回复
按前3列分组，统计不同的两列组合记录数
中国风 2014-11-19
  • 打赏
  • 举报
 回复
分析数据可以这样用 
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
中国风 2014-11-19
  • 打赏
  • 举报
 回复
用Group by 就行了，distinct没意义
nantian_service 2014-11-19
  • 打赏
  • 举报
 回复
谢谢回帖的各位高手. 我想要的结果就是6楼和8楼的高手回答的那样. 主要是原始数据太混乱. 一种物品在各个列都有可能存在, 只好用这样的方法来统计了.
在路上_- 2014-11-19
  • 打赏
  • 举报
 回复
楼主要的是不是这个效果？ 

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
在路上_- 2014-11-19
  • 打赏
  • 举报
 回复
更正 

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
在路上_- 2014-11-19
  • 打赏
  • 举报
 回复 

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
还在加载中灬 2014-11-19
  • 打赏
  • 举报
 回复
引用 7 楼 nantian_service 的回复:
完整的语句如下, 

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 行 ')' 附近有语法错误。 不太明白,( )是一对一了,哪里的语法错了?
最后面加个表别名
nantian_service 2014-11-19
  • 打赏
  • 举报
 回复
完整的语句如下, 

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 行 ')' 附近有语法错误。 不太明白,( )是一对一了,哪里的语法错了?
【SQL Server】sql中去除重复的数据 select distinct * from table
总的思路就是先找出表中重复数据中的一条数据，插入临时表中，删除所有的重复数据，然后再将临时表中的数据插入表中。所以重点是如何找出重复数据中的一条数据，有三种情况 1.重复数据完全一样，使用distinct select distinct * from table 2.id列不同,id类型为int,自增字段,使用聚合函数max或其他 select * from table where id in(...
数据库之--count/distinct/group by的用法总结
一、 count、distinct、group by的用法： 1.count()函数是用来统计表中记录的一个函数，返回匹配条件的行数，不去重。 2.count()语法： （1）count(*)—包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，包含NULL的记录，且不去重。 （2）count(1)—第一列，1表示一个固定值，也可以用count(2)、count(3)代替，在统计结...
distinct和count(*)合用会有问题
之前在一条MysqL查询语句里出现了莫名其妙的问题，然后经过网上一番搜索，终于发现是distinct和count(*)合用出现了问题，以下转载别人的内容：实验：查询一个column的无重复记录，需要知道有多少条记录，并显示记录。统计记录用count(*)函数，无重复记录distinct，以emp表为例。（1）先查询无重复记录[@more@]SQL>select distinct emp.sal f
Mysql查询(where 和group by)、筛选(having)与排序（order by）
Mysql查询子句 普通查询 select 列名1…列名n from 表名 2.精确条件查询 select 列名1…列名n from 表名 where 某列=值； select 列名1…列名n from 表名 where 某列>值； select 列名1…列名n from 表名 where 某列!=值； 3.模糊条件查询 select * from 表名 where 列名 like 值；...
数据库的查询操作
数据库的查询时学习数据库的核心： （1）最基础的查询语句：select * from 表名 ----from关键字后面写表名，表明数据来源于这张表 ----select后面写表中的列名，如果是 * 表示在结果中显示表中的所有列，后面也可以接部分的列名查询部分的数据，如果需要查询多个列之间使用逗号分隔 （2）消除重复的行 ----在select后面列前使用distinct可以消除重复的行：s...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章