什么呀全测试通不过,
给正确答案
====
drop table table1
go
CREATE TABLE Table1
(
COL1 VARCHAR(10),
COL2 VARCHAR(10),
COL3 VARCHAR(10)
)
go
INSERT INTO Table1
SELECT 'aa','Ha','0' UNION ALL
SELECT 'bb','GG','0' UNION ALL
SELECT 'aa','Ha','1' UNION ALL
SELECT 'cc','Ma','0' UNION ALL
SELECT 'bb','GG','1' UNION ALL
SELECT 'aa','Ha','a' UNION ALL
SELECT 'cc','Ma','a' UNION ALL
SELECT 'bb','GG','0'
go

SELECT * FROM Table1 WHERE COL1+COL2 IN(
SELECT COL1+COL2 FROM Table1 GROUP BY COL1,COL2 HAVING COUNT(1)<>3 OR (COUNT(1)=3 AND MAX(COL3)<>'a'))
UNION ALL
SELECT COL1,COL2,'a' COL3 FROM Table1 GROUP BY COL1,COL2 HAVING COUNT(1)=3 AND MAX(COL3)='a'
-----------------================================================
drop table A,B
go
CREATE TABLE A
(
ID INT,
NAME VARCHAR(10),
ScorID VARCHAR(10),
Fengxu INT
)
go
CREATE TABLE B
(
ID VARCHAR(10),
ScName VARCHAR(10)
)
go

INSERT INTO A
SELECT 1,'张三','001',50 UNION ALL
SELECT 2,'李四','005',90 UNION ALL
SELECT 3,'张三','004',52 UNION ALL
SELECT 4,'王二','003',25 UNION ALL
SELECT 5,'麻子','002',62 UNION ALL
SELECT 6,'李四','001',21
go
INSERT INTO B
SELECT '001','英语' UNION ALL
SELECT '002','数学' UNION ALL
SELECT '003','物理' UNION ALL
SELECT '004','化学' UNION ALL
SELECT '005','理工'
go
select * from A
select * from B
go
select case when grouping(ScorID)=0 then ''else + end 名,
isnull((select ScName from b b where b.id=a.ScorID),'') 课,
case when grouping(ScorID)=1 then ''else cast(sum(Fengxu)as varchar(10)) end 分
from a a group by name,ScorID WITH ROLLUP having grouping(name)=0
order by name,case when ScorID is null then '001' else name end
go

第1题我的代码明明出来的答案是对的 和你的一样 就是行的顺序变了一下

第2题出来的是不对。。。不过你贴的代码第2题根本就没法运行

晕,不可能

第一题我的也是对的,我自己都是运行过的,第二题我上次就说过结果都在排序错误

棉花糖ONE 这就是你给的结果测试的答案,不只是排序错误,完全就是另一会事,数据都不全

drop table A,B
go
CREATE TABLE A
(
ID INT,
NAME VARCHAR(10),
ScorID VARCHAR(10),
Fengxu INT
)
go
CREATE TABLE B
(
ID VARCHAR(10),
ScName VARCHAR(10)
)
go

INSERT INTO A
SELECT 1,'张三','001',50 UNION ALL
SELECT 2,'李四','005',90 UNION ALL
SELECT 3,'张三','004',52 UNION ALL
SELECT 4,'王二','003',25 UNION ALL
SELECT 5,'麻子','002',62 UNION ALL
SELECT 6,'李四','001',21
go
INSERT INTO B
SELECT '001','英语' UNION ALL
SELECT '002','数学' UNION ALL
SELECT '003','物理' UNION ALL
SELECT '004','化学' UNION ALL
SELECT '005','理工'
go
select
(case when A.id in (select max(id) from A group by A.name)
then name else ''end) as name ,B.ScName,A.fengxu from A
inner join B on B.id=A.ScorID

name ScName fengxu
---------- ---------- -----------
英语 50
理工 90
张三 化学 52
王二 物理 25
麻子 数学 62
李四 英语 21

(6 行受影响)

晕原先,以为是排版问题,你给的语句有点问题,我帮你改了
select case when grouping(ScorID)=0 then ''else name end 名,
isnull((select ScName from b b where b.id=a.ScorID),'') 课,
case when grouping(ScorID)=1 then ''else cast(sum(Fengxu)as varchar(10)) end 分
from a a group by name,ScorID WITH ROLLUP having grouping(name)=0
order by name,case when ScorID is null then '001' else name end
go

order by name,case when ScorID is null then '001' else name end --这句帮解释下

select case when grouping(ScorID)=0 then ''else name end 名,
isnull((select ScName from b b where b.id=a.ScorID),'') 课,
case when grouping(ScorID)=1 then ''else cast(sum(Fengxu)as varchar(10)) end 分
from a a group by name,ScorID WITH ROLLUP having grouping(name)=0
order by name,case when ScorID is null then '001' else name end

这整个语句我都还不太理解
帮助详解下

晕不理解也敢贴不来,去查帮助吧