❶ 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;