我写了好长一串 能不能简化一下啊

[cdisk]

用的PUBS库

SELECT
	job_desc
FROM
	dbo.jobs
WHERE
	job_id = 
	(
	SELECT
		job_id
	FROM	
			(
			SELECT	
				job_id,COUNT(*) Cnt
			FROM	
				dbo.employee
			GROUP BY 
				job_id
			) TEMP
	WHERE
		Cnt = (SELECT	
			MAX(Cnt)
		FROM
			(
			SELECT	
				job_id,COUNT(*) Cnt
			FROM	
				dbo.employee
			GROUP BY 
				job_id
			) TEMP
		)
	)

我感觉复杂了点啊 , 能不能简化一下怎么写呢 ?

[Bruce_Qiu]

SELECT a.*
FROM dbo.jobs AS a
	INNER JOIN (
		SELECT job_id 
		FROM dbo.employee 
		GROUP BY job_id 
		HAVING count(1)=(
			SELECT TOP 1 count(1) AS cnt 
			FROM dbo.employee
			GROUP BY job_id
			ORDER BY count(1) DESC)) as b 
	ON a.joi_id=b.job_id

不好意思， 没有帮你写出最短的。

[yanzuoguang]

select top 1 a.job_desc
from jobs as a
inner join
(
SELECT
job_id,COUNT(*) Cnt
FROM
dbo.employee
GROUP BY
job_id
)b
on a.job_id=b.job_id
order by b.Cnd desc

[yanzuoguang]

最简单的就这样了,假如还要再简单的就没有了....你那个思想很正确.不过这个SQL语句是简单了.但是执行效率低了一点点,执行效率和 jobs 有关

[yanzuoguang]

不过 我这语句有个好处就是可以查询多条,比如说查询第二多的,第3多的. 只要改下 top 1 就OK 了

[Bruce_Qiu]

[Quote=引用 10 楼 yanzuoguang 的回复:]
select top 1 a.job_desc
from jobs as a
inner join
(
SELECT
job_id,COUNT(*) Cnt
FROM
dbo.employee
GROUP BY
job_id
)b
on a.job_id=b.job_id
order by b.Cnd desc
[/Quote]

你这一条有问题， 你不能预知有多少个job的count时候最大的并且是相等的

[ERR0RC0DE]

这样??

select job_desc
from dbo.jobs
where job_id = 
(
	select job_id from 
	(
		select top 1 job_id, count(*) as cnt from employee
		group by job_id
		order by 2 desc
	) Z
)

[guguda2008]

条件中的子查询可以用JOIN代替吧

[tanshi]

JOIN

[--小F--]

可以把SELECT
job_id,COUNT(*) Cnt
FROM
dbo.employee
GROUP BY
job_id
作为一个临时表

2005直接写到CTE里面

[cdisk]

join 怎么用啊 , 我用的2000啊 可以建临时表吗 ?

[--小F--]

select * into #t from (SELECT job_id,COUNT(*) Cnt FROM dbo.employee GROUP BY job_id) t 

[Zoezs]

你这写的我咋发现有问题？

    job_id = ---这里能用等于吗？
    (
    SELECT
        job_id
    FROM    
            (
            SELECT    
                job_id,COUNT(*) Cnt
            FROM    
                dbo.employee
            GROUP BY 
                job_id
            ) TEMP

[cdisk]

#6 能啊 , 运行都没错 ,

[lrjt1980]

挺难噢。