❶ sql语句复杂排序问题，在线请高手解答
CREATETABLE#temp(
TYPE char(1),
INDINAME INT,
INDIVALUEINT
);
INSERTINTO#tempVALUES('A',1001,6);
INSERTINTO#tempVALUES('A',1001,2);
INSERTINTO#tempVALUES('A',1002,3);
INSERTINTO#tempVALUES('A',1003,5);
INSERTINTO#tempVALUES('B',1001,1);
INSERTINTO#tempVALUES('B',1002,4);
INSERTINTO#tempVALUES('B',1002,3);
INSERTINTO#tempVALUES('B',1003,6);
INSERTINTO#tempVALUES('C',1001,4);
INSERTINTO#tempVALUES('C',1002,6);
INSERTINTO#tempVALUES('C',1003,2);
GOSELECT
temp.TYPE,
temp.INDINAME,
MAX(temp.INDIVALUE)ASINDIVALUE
FROM
#temptemp
JOIN
(
SELECT
t.TYPE,
ROW_NUMBER()OVER(ORDERBYMAX(INDIVALUE)DESC)ASxNo
FROM
#tempt
WHERE
t.INDINAME=1001
GROUPBY
t.TYPE
)SubQuery1001
ON(temp.TYPE=SubQuery1001.TYPE)
GROUPBY
SubQuery1001.xNO,
temp.TYPE,
temp.INDINAME
ORDERBY
SubQuery1001.xNO,
temp.INDINAME
GO
TYPEINDINAMEINDIVALUE
--------------------------
A10016
A10023
A10035
C10014
C10026
C10032
B10011
B10024
B10036
(9行受影响)
SELECT
temp.TYPE,
temp.INDINAME,
MAX(temp.INDIVALUE)ASINDIVALUE
FROM
#temptemp
JOIN
(
SELECT
t.TYPE,
ROW_NUMBER()OVER(ORDERBYMAX(INDIVALUE)DESC)ASxNo
FROM
#tempt
WHERE
t.INDINAME=1003
GROUPBY
t.TYPE
)SubQuery1001
ON(temp.TYPE=SubQuery1001.TYPE)
GROUPBY
SubQuery1001.xNO,
temp.TYPE,
temp.INDINAME
ORDERBY
SubQuery1001.xNO,
temp.INDINAME
GO
TYPEINDINAMEINDIVALUE
--------------------------
B10011
B10024
B10036
A10016
A10023
A10035
C10014
C10026
C10032
(9行受影响)
❷ sql的复杂查询问题：
no，date，attendance
12 2010-10-10 Y
13 2010-11-12 N
1)
思路：先查工资大于8000，再并列查询，小于5次
2010年小于5次的
select A.no count(A.no) from Attend A where attendance group by substring(date,0,4) having count(A.no)<5
故，综上得出结论
select E.no,E.name from Employee E left join Wage W on E.no=W.no left join （select A.no count(A.no) from Attend A where attendance group by substring(date,0,4) having count(A.no)<5）A on E.no=A.no where W.amount > 8000;
2)
思路：先查工资平均数，分别查不同的日期即可
select W.amount/sum(W.amoun),A.no,A.attendance/sum(A.attendance) from Attend A left join Wage W on W.no=A.no where substring(date,0,6)='201212' group by A.no
select W.amount/sum(W.amoun),A.no,A.attendance/sum(A.attendance) from Attend A left join Wage W on W.no=A.no where substring(date,0,6)='201201' group by A.no
❸ SQL语句问题，复杂啊~~~
select a.class, count(*) from A a,B b where a.name = b.content group by a.class
❹ 复杂SQL语句提问
select
distinct
s_id,convert(varchar(100),
开始时间,
23)
f_time)
as
'日期',count(s_id)
as
'访问量',s_price*count(s_id)
as
'收入'
where
f_time
between
开始时间
and
结束时间
group
by
s_id
order
(收入是
s_id
外键里的
s_price
*
访问量)这句说得让人摸不着头脑~
这上面这句有可能有错误，但是起码比楼上那位对一点~
可以再来探讨一下~
❺ 过于复杂的sql语句有哪些缺陷
过于复杂的sql语句有哪些缺陷
不同的数据库甚至相同数据库的不同版本都可能不一样，具体可以查询联机帮助，或参阅产品规格说明。总的来说SQL语句的最大长度限制都是很大的，编写SQL语句一般不需要考虑语句的长度问题。例如ACCESS的SQL最大长度约为6,4000个、MSSQL为65,536 * 网络数据包。像这样的长度，足够你写下长篇大论了。但是话要说回来，一个太长的语句其执行效率变得会低下，尽量避免编写太长和过于复杂的SQL语句还是非常必要的。
❻ 一个很复杂的SQL语句问题,超级达人请进.
很简单呀：
SELECT DPATH,PV=COUNT(1),IP=count(distinct dip) from tablename group by dpath
测试了，没问题！
以上是SQL SERVER的实现。
==========ACCESS 这样：
SELECT DPATH,(SELECT COUNT(1) FROM TABLENAME WHERE TABLENAME.DPATH=TTMP.C) AS PV,COUNT(1) AS IP
FROM [SELECT DISTINCT DPATH, DIP
FROM TABLENAME]. AS TTMP
GROUP BY DPATH;