select arq=case when a.srt=1 then a.rq else '' end,sl,flag from (
select 1 as srt,rq,'' as sl,'' as flag from (select distinct rq from [table]) a
union all
select 2,rq,cast(sl as varchar),flag from [table]) a order by rq,srt
select 1 as srt,rq,'' as sl,'' as flag from (select distinct rq from [table]) a
union all
select 2,rq,cast(sl as varchar),flag from [table]) a order by rq,srt
2004-05-01 11 2 A
2004-05-01 22 3 A
2004-05-01 15 3 B
2004-05-02 12 3 A
2004-05-02 16 2 A
2004-05-03 21 1 A需要取得的结果是
select * from table where flag = 'A' 也就是
rq sl je flag
2004-05-01 11 2 A
2004-05-01 22 3 A
2004-05-02 12 3 A
2004-05-02 16 6 A
2004-05-03 21 1 A但是显示的格式希望是分了组的 (如下)2004-05-01 33 5 A //这里是分组合计
11 2 A
22 3 A2004-05-02 28 9 A //这里是分组合计
12 3 A
16 6 A
2004-05-03 21 1 A //这里是分组合计
21 1 A想用一个SQL语句就搞定,如果能写成视图最好。请高手帮忙,
谢谢zheninchangjiang(徐震) ,谢谢!
select 1 as srt,rq,'' as sl,'' as flag from (select distinct rq from [ttt]) a
union all
select 2,rq,cast(sl as varchar),flag from [ttt]
SELECT TOP 100 PERCENT *
FROM (SELECT rq, SUM(CONVERT(float, sl)) AS sl, flag
FROM view1
GROUP BY rq, flag
UNION ALL
SELECT rq, sl, flag
FROM view1) a
WHERE (flag = 'A')
ORDER BY rq, sl DESC
谢谢你的帮助.可是你的写法得不到我的要求数据阿.谢谢帮助!
(select a.rq,sum(a.sl) as sl,sum(a.je) as je,'A' as flag from (select * from [table] where flag = 'A' ) a group by rq
union
select * from [table] where flag = 'A') b order by rq asc,sl desc
------------------------------------------------------ ------------------------------ ------------------------------ ----
2004-05-01 00:00:00.000 33.00 5.00 A
2004-05-01 00:00:00.000 22.00 3.00 A
2004-05-01 00:00:00.000 11.00 2.00 A
2004-05-02 00:00:00.000 28.00 9.00 A
2004-05-02 00:00:00.000 16.00 6.00 A
2004-05-02 00:00:00.000 12.00 3.00 A
2004-05-03 00:00:00.000 21.00 1.00 A(7 row(s) affected)
rq sl je flag
------------------------------------------------------ -----
2004-05-01 00:00:00.000 33.00 5.00 A
2004-05-01 00:00:00.000 22.00 3.00 A
2004-05-01 00:00:00.000 11.00 2.00 A
2004-05-02 00:00:00.000 28.00 9.00 A
2004-05-02 00:00:00.000 16.00 6.00 A
2004-05-02 00:00:00.000 12.00 3.00 A
2004-05-03 00:00:00.000 21.00 1.00 A(7 row(s) affected)
2004-5-2 00:00:00 44.00 3.00 A
2004-5-3 00:00:00 66.00 1.00 A
=====================================
表结构
CREATE TABLE dbo.abc
(
rq datetime NOT NULL,
sl money NOT NULL,
je money NULL,
flag char(1) NOT NULL
)
go表内容:
2004-5-1 00:00:00 11.0000 2.0000 A
2004-5-1 00:00:00 22.0000 3.0000 B
2004-5-1 00:00:00 33.0000 3.0000 C
2004-5-2 00:00:00 44.0000 3.0000 A
2004-5-2 00:00:00 55.0000 2.0000 B
2004-5-3 00:00:00 66.0000 1.0000 ASQL语法:
select rq,cast(sl as varchar) as sl,cast(je as varchar) as je ,flag from
(select a.rq,sum(a.sl) as sl,sum(a.je) as je,'A' as flag from (select * from [abc] where flag = 'A' ) a group by rq
union
select * from [abc] where flag = 'A') b order by rq asc,sl desc
数据库 SQL 2000再请pressman(行者) 帮忙看看,万分感谢!
rq sl je flag
2004-05-01 11 2 A
2004-05-01 22 3 A
2004-05-02 12 3 A
2004-05-02 16 6 A
2004-05-03 21 1 A那你这个是怎么来的呢??
CREATE TABLE dbo.abc (
rq datetime NOT NULL,
sl money NOT NULL,
je money NULL,
flag char(1) NOT NULL
)
insert abc select '2004-5-1 00:00:00',11.0000,2.0000,'A'
union all select '2004-5-1 00:00:00',22.0000,3.0000,'B'
union all select '2004-5-1 00:00:00',33.0000,3.0000,'C'
union all select '2004-5-2 00:00:00',44.0000,3.0000,'A'
union all select '2004-5-2 00:00:00',55.0000,2.0000,'B'
union all select '2004-5-3 00:00:00',66.0000,1.0000,'A'
go--flag也分组
select rq,sl,je,flag
from(
select rq=convert(char(10),rq,120),sl=sum(sl),je=sum(je),flag
,s1=convert(char(10),rq,120),s2=flag,s3=0
from abc group by convert(char(10),rq,120),flag
union all
select '',sl,je,flag
,s1=convert(char(10),rq,120),s2=flag,s3=1
from abc
)a order by s1,s2,s3
go--删除测试
drop table abc/*--测试结果rq sl je flag
---------- --------------------- --------------------- ----
2004-05-01 11.0000 2.0000 A
11.0000 2.0000 A
2004-05-01 22.0000 3.0000 B
22.0000 3.0000 B
2004-05-01 33.0000 3.0000 C
33.0000 3.0000 C
2004-05-02 44.0000 3.0000 A
44.0000 3.0000 A
2004-05-02 55.0000 2.0000 B
55.0000 2.0000 B
2004-05-03 66.0000 1.0000 A
66.0000 1.0000 A(所影响的行数为 12 行)
--*/
CREATE TABLE dbo.abc (
rq datetime NOT NULL,
sl money NOT NULL,
je money NULL,
flag char(1) NOT NULL
)
insert abc select '2004-5-1 00:00:00',11.0000,2.0000,'A'
union all select '2004-5-1 00:00:00',22.0000,3.0000,'B'
union all select '2004-5-1 00:00:00',33.0000,3.0000,'C'
union all select '2004-5-2 00:00:00',44.0000,3.0000,'A'
union all select '2004-5-2 00:00:00',55.0000,2.0000,'B'
union all select '2004-5-3 00:00:00',66.0000,1.0000,'A'
go--flag不分组
select rq,sl,je,flag
from(
select rq=convert(char(10),rq,120),sl=sum(sl),je=sum(je),flag=''
,s1=convert(char(10),rq,120),s2='',s3=0
from abc group by convert(char(10),rq,120)
union all
select '',sl,je,flag
,s1=convert(char(10),rq,120),s2=flag,s3=1
from abc
)a order by s1,s2,s3
go--删除测试
drop table abc/*--测试结果rq sl je flag
---------- --------------------- --------------------- ----
2004-05-01 66.0000 8.0000
11.0000 2.0000 A
22.0000 3.0000 B
33.0000 3.0000 C
2004-05-02 99.0000 5.0000
44.0000 3.0000 A
55.0000 2.0000 B
2004-05-03 66.0000 1.0000
66.0000 1.0000 A(所影响的行数为 9 行)
--*/
zjcxc(邹建)这样应该可以搞定了吧,:)
语法非常正确。
另外请教如何将其写成一个view呢?这样我就可以对这个VIEW操作。
查询等复杂操作。如果不行也没有关系,我已经很满足了。谢谢。
你是我的偶像,感谢您以前的帮助。以后还请多多执教。
让大家看看,待会揭帖。
as
select top 100 percent rq,sl,je,flag
from(
select rq=convert(char(10),rq,120),sl=sum(sl),je=sum(je),flag=''
,s1=convert(char(10),rq,120),s2='',s3=0
from abc group by convert(char(10),rq,120)
union all
select '',sl,je,flag
,s1=convert(char(10),rq,120),s2=flag,s3=1
from abc
)a order by s1,s2,s3
怎么很难看懂啊。同样的结果。
看来我要好好努力了。
CREATE TABLE dbo.abc (
rq datetime NOT NULL,
sl money NOT NULL,
je money NULL,
flag char(1) NOT NULL
) 其实是用来说明一下问题的,真正地表还是有主建的。应该是这样CREATE TABLE dbo.abc
(
id numeric(18,0) IDENTITY,
rq datetime NOT NULL,
sl money NOT NULL,
je money NULL,
flag char(1) NOT NULL
)
go请问如何才能将id 列也列在这个视图里面?因为没有主建,该视图无法更新数据库的。我写成create view 视图名
as
select top 100 percent id,rq,sl,je,flag
from(
select id,rq=convert(char(10),rq,120),sl=sum(sl),je=sum(je),flag=''
,s1=convert(char(10),rq,120),s2='',s3=0
from abc group by convert(char(10),rq,120)
union all
select id,'',sl,je,flag
,s1=convert(char(10),rq,120),s2=flag,s3=1
from abc
)a order by s1,s2,s3结果不行,提示列'abc.id' 在选择表中无效,因为该列既不包含在聚合函数中,
也不包含在group by 子句中。写成select id,rq,sl,je,flag
from(
select id,rq=convert(char(10),rq,120),sl=sum(sl),je=sum(je),flag=''
,s1=convert(char(10),rq,120),s2='',s3=0
from abc group by id,convert(char(10),rq,120)
union all
select id,'',sl,je,flag
,s1=convert(char(10),rq,120),s2=flag,s3=1
from abc
)a order by s1,s2,s3也不行请再拉我一把。谢谢
from(
select rq=convert(char(10),rq,120),sl=sum(sl),je=sum(je),flag='',id=0
,s1=convert(char(10),rq,120),s2='',s3=0,s4=0
from abc group by convert(char(10),rq,120)
union all
select '',sl,je,flag,id
,s1=convert(char(10),rq,120),s2=flag,s3=1,s4=id
from abc
)a order by s1,s2,s3,s4
go好像可以了。
我查了sqlserver帮助。解释如下:
如果一个 SELECT 语句既包含 TOP 又包含 ORDER BY 子句,那么返回的行将会从排序后的结果集中选择。整个结果集按照指定的顺序建立并且返回排好序的结果集的前 n 行。请教高手们,是不是只能有100条记录啊?如果我有10000条记录该怎么办呢?
TO ghost_old(PB初级程序员),此人好象什么都不懂,什么都要问...