执行这条SQL
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID = 'ID1'
Group by ID,Name
结果集为
85,ID1,xxx执行这条SQL
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
结果集为
64,ID2,xxx
103,ID3,xxx
...
44,ID4,xxx其中第一条SQL中的ID有可能是第二条SQL中ID集合的元素,也有可能不是现在的问题是,我想将两条SQL合并成一条SQL
然后只出一个二维表
85,ID1,xxx
64,ID2,xxx
103,ID3,xxx
...
44,ID4,xxx
有哪位高人指点一下
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID = 'ID1'
Group by ID,Name
结果集为
85,ID1,xxx执行这条SQL
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
结果集为
64,ID2,xxx
103,ID3,xxx
...
44,ID4,xxx其中第一条SQL中的ID有可能是第二条SQL中ID集合的元素,也有可能不是现在的问题是,我想将两条SQL合并成一条SQL
然后只出一个二维表
85,ID1,xxx
64,ID2,xxx
103,ID3,xxx
...
44,ID4,xxx
有哪位高人指点一下
解决方案 »
- 竖表转横表的问题
- 超难超复杂的sql 数据格式转换问题。。。。
- 有简单代码,比较急,在线等;在存储过程中,传入DateTime类型的数据,在sql语句中能执行;可是在将sql定义为字符串的时候再execute(@sq
- 关于一个存储过程的优化!求教高手
- 存储过程返回记录集问题
- 在设计时用varchar还是用nvarchar?请小黑,蚂蚁,大力等高手帮忙
- 一判断查询,求大家帮忙解决!!!!!!!!解决结帖.
- 关于UPDATE触发器的问题
- 如何通过存储过程向sql中insert image型数据?
- 这个SQL怎么写
- Connection指针返回Recordset指针引发崩溃
- 新手问题,sql 颜色问题,谢谢
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID = 'ID1'
Group by ID,Nameunion allSelect Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID = 'ID1'
Group by ID,Name
union
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID1','ID2','ID3',...'ID5')
Group by ID,Name
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID = 'ID1'
Group by ID,Name
union all
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID1','ID2','ID3',...'ID5')
Group by ID,Name
有的Name只对应一个ID 而有点Name对应多个ID不太好控制怎样来union啊
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
如果你要把第一个语句出现的记录重复出现在总记录中,用就下面的
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID = 'ID1'
Group by ID,Nameunion allSelect Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID1','ID2','ID3',...'ID5')
Group by ID,Name
用union all连接查询的结果集
Where CreateDate > '20080201' and CreateDate<'20080401' and
(Name = 'xxx' and ID = 'ID1')
or (Name = 'xxx' and ID in ('ID2','ID3',...'ID5'))
or (Name = 'yyy' and ID = 'IDxxxxx')
or ...
Group by ID,Name
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID1','ID2','ID3',...'ID5')
Group by ID,Name
Select Count(*),ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID1','ID2','ID3',...'ID5')
Group by ID,Name
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID1','ID2','ID3',...'ID5')
Group by ID,Name
select count(cnt),ID,Name
from (
Select Count(*) as cnt,ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID = 'ID1'
Group by ID,Name union all Select Count(*) as cnt,ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
) a
group by ID ,Name
select sum(cnt),ID,Name
from (
Select Count(*) as cnt,ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID = 'ID1'
Group by ID,Name union all Select Count(*) as cnt,ID,Name From Table1
Where CreateDate > '20080201' and CreateDate<'20080401'
and Name = 'xxx'
and ID in ('ID2','ID3',...'ID5')
Group by ID,Name
) a
group by ID ,Name