碰到两个小题，大家帮想下 - 编程论坛

#2

初学Delphi2007-02-09 14:51

删除不掉了吧？
修改也修改不成，
在查询分析器里删除吧。。。怎么你的表没加主键

#3

Kendy1234562007-02-09 15:35

1. select distinct * into #1 from table1
delete table1 from table1 a join
(select col1,col2,count(*) as cnt from #1 group by col1,col2 having count(*) =3) b
on a.col1 =b.col1 and a.col2=b.col2 and a.col3 <>'a'

#4

Kendy1234562007-02-09 15:47

2. select * into #1 from tableA
insert into #1 (name,scorID, fenxu)
select distinct name,'','' from tableA

select name = case when scname = '' then name else '' end,
scname = isnull(scname,''),
fenxu = isnull(fenxu,'')
from #1 a
left join TableB b on a.scorID =b.ID

#5

棉花糖ONE2007-02-09 16:37

1.

delete shiyan1 from shiyan1 s ,(select col1,col2,count(1) as c from shiyan1 where col3='a' group by col1,col2 ) s2 where s.col1=s2.col1 and s.col2=s2.col2 and s.col3='a'

2.
select (case when A.id in (select max(id) from A group by A.name) then name else ''end) as name ,B.ScName,A.fenshu from A inner join B on B.id=A.Scoreid

#6

初学Delphi2007-02-09 16:51

第二题我用游标实现的
结果就是你那个界面的一样

declare 游标 scroll cursor for
select distinct name from biao
open 游标
declare @a varchar(1000)
declare @b varchar(8000)
declare @c int
set @c=0
set @b=''
while @@FETCH_STATUS=0
begin
if @c=(select count(distinct name ) from biao )
break
set @c=@c+1
fetch next from 游标 INTO @a
select @b=@b+' union all select '''+@a+''' as 姓名,'' '' as 科目,'' '' as 分数 '
select @b=@b+' union all select '' '' as 姓名,biao1.kemu,biao.fenshu from biao,biao1 where name='''+@a+''' and biao.kemu=biao1.id'
end
set @b=right(@b,len(@b)-10)
exec (@b)
close 游标
deallocate 游标

没想到什么好方法，一会看看斑竹的

#7

棉花糖ONE2007-02-09 16:54

我的结果记录都在,但是排版和他的不一样

#8

Kendy1234562007-02-09 17:07

我写出来自己没测就贴了

#9

棉花糖ONE2007-02-09 17:26

得规定一下得把建表的代码给出来,我建表的时间比写的时间还多

#10

accpfriend2007-02-09 17:35

测试下,下周再说

#11

accpfriend2007-02-28 13:51

什么呀全测试通不过,
给正确答案
====
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

#12

Kendy1234562007-02-28 16:03

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

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

#13

accpfriend2007-03-01 09:05

晕,不可能

#14

棉花糖ONE2007-03-01 09:15

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

#15

accpfriend2007-03-01 09:28

棉花糖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 行受影响)

#16

棉花糖ONE2007-03-01 10:21

晕原先,以为是排版问题,你给的语句有点问题,我帮你改了
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

#17

accpfriend2007-03-02 08:48

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

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

#18

棉花糖ONE2007-03-02 10:03

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