100分求一条SQL语句,在线等.

[linuxjava01]

ID, Name ,Counts
1 Linux 10
1 linux 30
2 Mac 40
3 Unix 30
3 Unix 40
3 Unix 60


要求取的结果是：ID, Name ,Counts
1 linux 30
2 Mac 40
3 Unix 60

[aaa_apple]

顶

[terryhuang]

select id,name,max(counts) as counts from table1 group by id,name

[mengmou]

--这个不对么?
select id,name,max(counts) as counts from table1 group by id,name

[江城老温]

select id,name,max(counts) as counts from table1 group by id,name

这个挺简单

[paoluo]

mengmou()mengmou()
whChina(江城老温)(as a thinker)

都沒看樓主下面的回復。

[jieaiyang2008]

学习

[q卡卡p]

select id,name,max(counts) from tb group by id,name

[linuxjava01]

樓主，測試這個語句看看

Select
Count(ID) As Count,
CateName
From
表 A
Where Not Exists(Select ID From 表 Where ID = A.ID And IsNull(CateID, 0) > IsNull(A.CateID, 0))
Group By
CateName

这个也不太好测试,因为哪个表A我也是费了10多行的sql语句查询出来的,所以无法直接A.ID这样使用,(能将查询出来的表重命名吗?)
表A实际上是这个语句查询出来的(
SELECT HRM004Employee.EmployeeID, HRM004Employee.EmployeeName,
HRM005Category.CateName, HRM005Category.CateID
FROM HRM001Organization INNER JOIN
HRM003DeptEmployee ON
HRM001Organization.OrgID = HRM003DeptEmployee.OrgID INNER JOIN
HRM004Employee ON
HRM003DeptEmployee.EmployeeID = HRM004Employee.EmployeeID AND
HRM004Employee.DeleteFlg = '0' AND HRM004Employee.StatusFlag IN ('0', '1')
INNER JOIN
HRM002Department ON
HRM001Organization.OrgID = HRM002Department.OrgID AND
HRM003DeptEmployee.DeptID = HRM002Department.DeptID LEFT OUTER JOIN
HRM010StudyExperience ON
HRM010StudyExperience.EmployeeID = HRM004Employee.EmployeeID LEFT OUTER JOIN
HRM005Category ON HRM005Category.CateType = '9' AND
HRM005Category.CateID = HRM010StudyExperience.Educational
WHERE (HRM001Organization.ActiveFlg = '1')
)

[yuemb369]

此表设计有冗余

[linuxjava01]

呵呵,设计的问题,我们这些新人也没办法啊.

[paoluo]

linuxjava01(各位旅客请注意：YY次列车就要启动了，请拉好扶手，系) ,

將你的語句生成一個臨時表，或者視圖。

然後再用生成的臨時表或者視圖來測試這段代碼

[crossrowman]

select counts,CateID,A.CateName from
(
select count(EmployeeID) counts ,CateID
from
(
select EmployeeID,max(CateID)
from
(
SELECT HRM004Employee.EmployeeID, HRM004Employee.EmployeeName,
HRM005Category.CateName, HRM005Category.CateID
FROM HRM001Organization INNER JOIN
HRM003DeptEmployee ON
HRM001Organization.OrgID = HRM003DeptEmployee.OrgID INNER JOIN
HRM004Employee ON
HRM003DeptEmployee.EmployeeID = HRM004Employee.EmployeeID AND
HRM004Employee.DeleteFlg = '0' AND HRM004Employee.StatusFlag IN ('0', '1')
INNER JOIN
HRM002Department ON
HRM001Organization.OrgID = HRM002Department.OrgID AND
HRM003DeptEmployee.DeptID = HRM002Department.DeptID LEFT OUTER JOIN
HRM010StudyExperience ON
HRM010StudyExperience.EmployeeID = HRM004Employee.EmployeeID LEFT OUTER JOIN
HRM005Category ON HRM005Category.CateType = '9' AND
HRM005Category.CateID = HRM010StudyExperience.Educational
WHERE (HRM001Organization.ActiveFlg = '1')
) X
group by EmployeeID
) Y
group by CateID
) Z
left outer join HRM005Category A on A.CateID = Z.CateID

[mengmou]

ID, CateName CateID
HX0028 本科 528
HX0029 NULL NULL
HX0030 NULL NULL
HX0031 NULL NULL
HX0032 研究生 529
HX0032 研究生 529
HX0032 研究生 529
HX0032 研究生 529
HX0033 大专 527
HX0033 研究生 529
HX0035 研究生 529
HX0036 NULL NULL
HX0040 NULL NULL
各位的速度好快啊.
不好意思,刚才的问题问错了.

取出来的数据是按学历分组的，相同id的取最高的学历（CateID越大，学历越高）

55 NULL
2 本科
1 博士
3 大专
10 研究生
1 中专
--------------------------------------------------------------------------------
看不明白

[divmedia]

select id,name,max(counts) as counts from table1 group by id,name

[linuxjava01]

linuxjava01(各位旅客请注意：YY次列车就要启动了，请拉好扶手，系) ,

將你的語句生成一個臨時表，或者視圖。

然後再用生成的臨時表或者視圖來測試這段代碼
---------------------------------------------
看来只能这样了.

非常感谢大家的帮忙.

明天揭贴.

[pastbee]

接分来

[abswswsw]

好！！！

[shanshaohappy]

create table t(ID int,Name varchar(20),Counts varchar(30))
go
insert into t
select 1,'linux',10 union all
select 1,'linux',30 union all
select 2,'Mac',40 union all
select 3,'unix',30 union all
select 3,'unix',30 union all
select 3,'unix',40 union all
select 3,'unix',60

go
select ID,Name,max(counts)as Counts from t group by ID,Name
go

drop table t

[Jennyby]

select ID, Name ,max(Counts) as Counts from 表 group by ID,Name

[smallnesshawk]

Select
Count(ID) As Count,
CateName
From
表 A
Where Not Exists(Select ID From 表 Where ID = A.ID And IsNull(CateID, 0) > IsNull(A.CateID, 0))
Group By
CateName