name & 合计可以这样写,小计不知是什么意思。Select name,count(*) as 合计 from
(
Select name from 表1
Union All
Select name from 表2
Union All
Select name from 表3
)temp
group by name
(
Select name from 表1
Union All
Select name from 表2
Union All
Select name from 表3
)temp
group by name
解决方案 »
- With RootCTE递归时,当满足条件后,如何跳出递归?
- 关于词词、成语的拆字搜索(我暂且这么叫,我也不太明白)
- 这个sql怎么写?
- 如何从本机把数据插入到另一台机器(数据结构中有一个是不同的)
- 面对update处理了多条记录_触发器只对一条记录进行处理
- 刚装了MS—SQL``但有点问题``很简单的``
- 两台服务器如何实现相互同步!急
- 请教专家下面的SQL语句哪里错了吗?不知道要怎么改才正确,请专家指点
- 急求大虾帮忙写一个sql语句的问题,谢谢谢谢谢谢!!!!!!!!!!!!!!!
- SQL语句 这句有是什么意思呢
- 有个设计时的问题?请进来帮帮忙.是自定义字段的.
- 存储过程前的USE pubs语句有什么用
select name,合计,小计一,合计-小计一 as 小计二
from(
select isnull(a.name,isnull(b.name,c.name)) as name
,isnull(a.aa,0)+isnull(b.aa,0)+isnull(c.aa,0) as 合计
,isnull(a.aa,isnull(b.aa,isnull(c.aa,0))) as 小计一
from(
select NAME,count(*) as aa from 表1 group by NAME
) a full join (
select NAME,count(*) as aa from 表2 group by NAME
) b on a.name=b.name full join (
select NAME,count(*) as aa from 表3 group by NAME
) c on a.name=c.name) a order by 合计 desc,name
下面是数据测试
*/
--创建数据测试环境
declare @tb1 table(ID int,[USER] varchar(1),NAME varchar(2))
declare @tb2 table(ID int,[USER] varchar(1),NAME varchar(2))
declare @tb3 table(ID int,[USER] varchar(1),NAME varchar(2))insert into @tb1
select 1,'A','中'
union all select 2,'B','有'insert into @tb2
select 1,'C','中'
union all select 2,'D','的'insert into @tb3
select 1,'F','有'
union all select 2,'E','我'--得到结果
select name,合计,小计一,合计-小计一 as 小计二
from(
select isnull(a.name,isnull(b.name,c.name)) as name
,isnull(a.aa,0)+isnull(b.aa,0)+isnull(c.aa,0) as 合计
,isnull(a.aa,isnull(b.aa,isnull(c.aa,0))) as 小计一
from(
select NAME,count(*) as aa from @tb1 group by NAME
) a full join (
select NAME,count(*) as aa from @tb2 group by NAME
) b on a.name=b.name full join (
select NAME,count(*) as aa from @tb3 group by NAME
) c on a.name=c.name) a order by 合计 desc,name/*-------------------------------------------------
上面语句的执行结果:
name 合计 小计一 小计二
---- ----------- ----------- -----------
有 2 1 1
中 2 1 1
的 1 1 0
我 1 1 0(所影响的行数为 4 行)
-----------------------------------------------------*/
是什么????
from(
select isnull(a.name,isnull(b.name,c.name)) as name
,isnull(a.aa,0)+isnull(b.aa,0)+isnull(c.aa,0)+isnull(d.aa,0)+isnull(e.aa,0) as 合计
,isnull(a.aa,isnull(b.aa,isnull(c.aa,isnull(d.aa,isnull(e.aa,0))))) as 小计一
from(
select NAME,count(*) as aa from 表1 group by NAME
) a full join (
select NAME,count(*) as aa from 表2 group by NAME
) b on a.name=b.name full join (
select NAME,count(*) as aa from 表3 group by NAME
) c on a.name=c.name full join (
select NAME,count(*) as aa from 表4 group by NAME
) d on a.name=d.name full join (
select NAME,count(*) as aa from 表5 group by NAME
) e on a.name=c.name) a order by 合计 desc,name
update # set @1=case when @2=name then @1+1 else 1 end,@2=name,flag2=@1set @sql = 'select name,count(*) 合计'
select @sql = @sql + ',max(case flag2 when '''+cast(flag2 as varchar(10))+''' then ''1(在第''+cast(flag as varchar(10))+''张表)'' else ''0(不在任何表)'' end) [小计'+cast(flag2 as varchar(10))+']'
from (select distinct flag2 from #) as a
select @sql = @sql+' from # group by name order by count(*) desc'exec(@sql)
create table #2 (ID int,[USER] varchar(1),NAME varchar(2))
create table #3 (ID int,[USER] varchar(1),NAME varchar(2))insert into #1 select 1,'A','中' union all select 2,'B','有' insert into #2 select 1,'C','中' union all select 2,'D','的' insert into #3 select 1,'F','有' union all select 2,'E','我'
------------------
declare @1 int,@2 varchar(10),@sql varchar(8000)select *,0 flag2 into # from (select *,0 flag from #1 union all select *,1 from #2 union all select *,3 from #3) tem order by name
update # set @1=case when @2=name then @1+1 else 1 end,@2=name,flag2=@1set @sql = 'select name,count(*) 合计'
select @sql = @sql + ',max(case flag2 when '''+cast(flag2 as varchar(10))+''' then ''1(在第''+cast(flag as varchar(10))+''张表)'' else ''0(不在任何表)'' end) [小计'+cast(flag2 as varchar(10))+']'
from (select distinct flag2 from #) as a
select @sql = @sql+' from # group by name order by count(*) desc'exec(@sql)go
drop table #1,#2,#3,#
select @sql = @sql + ',max(case flag2 when '''+cast(flag2 as varchar(10))+''' then ''1(在第''+cast(flag as varchar(10))+''张表)'' else ''0(不在任何表)'' end) [小计'+cast(flag2 as varchar(10))+']' from (select distinct flag2 from #) aexec('select *,0 flag2 into # from ('+@sql2+' ) tem order by name declare @1 int,@2 varchar(10) update # set @1=case when @2=name then @1+1 else 1 end,@2=name,flag2=@1 '+@sql+' from # group by name order by count(*) desc')