请问两条SQL语句。。

[icecsdn]

1，表如下
id name conse gread
1 刘力 物理 80
2 刘力 化学 75
3 刘力 体育 92
4 刘力 物理 80
5 刘力 化学 75
6 刘力 物理 80

怎么删除多余重复的行？
2，表如下
name conse gread
王1 化学 82
王1 物理 62
王1 体育 68
王2 化学 72
王2 物理 92
王2 体育 66
变换成如下形式，平均直保留一位小数，用一条SQL语句实现
姓名 物理 化学 体育 平均成绩
王1 62 82 68 78.1
王2 92 72 66 75.3
谢谢~~

[CrazyFor]

一,

---------------
如果有ID字段，就是具有唯一性的字段

delect table where id not in (

select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。



2,如果是判断所有字段也可以这样
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa



3，没有ID的情况

select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp


col1+','+col2+','...col5 联合主键


select * from table where col1+','+col2+','...col5 in (

select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。

2,
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)

[CrazyFor]

二,参考:

create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)

declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a

select @sql = left(@sql,len(@sql)-1) + ' from #'

exec(@sql)

drop table #

[qianguob]

第二个问题。
Select name,sum(case when conse = '物理' then gread else 0 end) as 物理,
sum(case when conse = '化学' then gread else 0 end) as 化学,
sum(case when conse = '体育' then gread else 0 end) as 体育
round(avg(gread),1) as 平均成绩
from yourTable group by name

[yangvxin1]

1.

delete from yourtable

where id not in
(
select max(id) from yourtable group by name,conse,gread

)

2.

--yourtable(name conse gread)
--
Select name,sum(case when conse = '物理' then gread else 0 end) as 物理,
sum(case when conse = '化学' then gread else 0 end) as 化学,
sum(case when conse = '体育' then gread else 0 end) as 体育,
CAST(avg(cast(gread as float)) AS decimal(10,1)) as 平均成绩

from yourtable group by name

或者
如果gread 为int的话。

declare @sql as varchar(3000)

set @sql = 'select name '

select @sql = @sql + ',sum(case conse when '''+conse+''' then gread else 0 end ) as ['+conse+'] '
from (select distinct conse from pp1) as a

set @sql=@sql+ ',CAST(avg(cast(gread as float)) AS decimal(10,1)) as 平均成绩'

set @sql=@sql+' from yourtable group by name'


exec (@sql)


3.

table1(id 姓名 性别 职务)
table2(编号 性别)
table3(编号 职务)

IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'FCXingBie')
DROP FUNCTION FCXingBie
GO

CREATE FUNCTION FCXingBie
(@p1 int)

RETURNS varchar(10)
AS

declare @p2 varchar(10)

select @p2=性别 from table2 where 编号=@p1
return @p2
GO


IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'FCZhiWu')
DROP FUNCTION FCZhiWu
GO

create function FCZhiWu(@p1 int)

returns varchar(10)
as
declare @p3 varchar(10)

select @p3=职务 from table3 where 编号=@p1

return @p3

GO

select id,姓名,dbo.FCXingBie(性别),dbo.FCZhiWu(职务) from table1

[icecsdn]

还有一个问题是如下
-------------------
id 姓名 性别 职务
1 王1 1 1
2 王2 0 2
-------------------
编号 性别
1 男
0 女
----------------
编号 职务
1 班长
2 学委
----------------
最后显示如下：
id 姓名 性别 职务
1 王1 男 班长
2 王2 女 学委
怎么实现？

[txlicenhe]

1: delete yourTable where id not in (select id from (Select name,conse,gread,min(id) as id from yourTable group by name,conse,gread) b )

[dotnba]

第一个问题，首先是找到重复行：
select A.*
from Table A
where
(select count(1)
from Table C
where A.Col1 = C.Col1 and A.Col2 = C.Col2 and ....A.ColN = C.ColN) > 1
稍微修改一下：
delete
from Table A
where
(select count(1)
from Table C
where A.Col1 = C.Col1 and A.Col2 = C.Col2 and ....A.ColN = C.ColN) > 1

[happydreamer]

1
删除重复数据

一、具有主键的情况
a.具有唯一性的字段id(为唯一主键)
delect table
where id not in
(
select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件，如只有col1，
那么只要col1字段内容相同即表示记录相同。

b.具有联合主键
假设col1+','+col2+','...col5 为联合主键
select * from table where col1+','+col2+','...col5 in (
select max(col1+','+col2+','...col5) from table
group by col1,col2,col3,col4
having count(*)>1
)
group by 子句后跟的字段就是你用来判断重复的条件，
如只有col1，那么只要col1字段内容相同即表示记录相同。


or
select * from table where exists (select 1 from table x where table.col1 = x.col1 and
table.col2= x.col2 group by x.col1,x.col2 having count(*) >1)

c:判断所有的字段
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa

二、没有主键的情况

a:用临时表实现
select identity(int,1,1) as id,* into #temp from ta
delect #temp
where id not in
(
select max(id) from # group by col1,col2,col3...
)
delete table ta
inset into ta(...)
select ..... from #temp

b:用改变表结构（加一个唯一字段）来实现
alter table 表 add newfield int identity(1,1)
delete 表
where newfield not in
(
select min(newfield) from 表 group by 除newfield外的所有字段
)

alter table 表 drop column newfield


2


declare @sql varchar(8000)
set @sql = 'select name,'

select @sql = @sql + 'sum(case conse when '''+conse+'''
then gread else 0 end) as ['+conse+'],'
from (select distinct conse from 表) a

select @sql = left(@sql,len(@sql)-1) + ' ,avg(gread) as 平均成绩 from 表 group by name'

print @sql

exec(@sql)
go

[txlicenhe]

2:
Select name,sum(case when conse = '物理' then gread else 0 end) as 物理,
sum(case when conse = '化学' then gread else 0 end) as 化学,
sum(case when conse = '体育' then gread else 0 end) as 体育
round(avg(gread),1) as 平均成绩
from yourTable group by name

[happydreamer]

select a.id,a.姓名,b.性别,c.职务
from table1 a join table2 b on a.性别=b.编号
join tabl3 c on a.职务=c.编号

[txlicenhe]

1: delete yourTable where id not in (select id from (Select name,conse,gread,min(id) as id from yourTable group by name,conse,gread) b )

2:
Select name,sum(case when conse = '物理' then gread else 0 end) as 物理,
sum(case when conse = '化学' then gread else 0 end) as 化学,
sum(case when conse = '体育' then gread else 0 end) as 体育
round(avg(gread),1) as 平均成绩
from yourTable group by name

3:
Select a.id,a.姓名,b.性别,c.职务
from a
join b on a.性别 = b.编号
join c on a.职务 = c.编号

[hp0773]

1、先找出没有重复行的结果：
select Max(id) as id,Max(name) as name,Max(gread) as gread,conse from table1 group by conse得以下结果：
id name conse gread
1 刘力 物理 80
2 刘力 化学 75
3 刘力 体育 92
然后就可以建个临时表，删除原表的记录，再将临时表的数据导入你的表中

[icecsdn]

谢谢回答我问题的朋友！有两个问题都解决了，只是我的第2个问题的解决办法我还看不懂，就是变成横行的那个问题，请问我到哪里查相关方面的资料，他是属于什么查询？

[yijiayi]

是属于交叉报表.你在论坛里搜索一下就可以找到很多这方面的讨论了.