m**********2
发帖数: 2252 | 1 SQL server 2005
运行下面的query:
SELECT p.[Code],
COUNT(*) AS leads,
SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
SUM (CASE WHEN (p.[Stage]='ENR' AND p.[ID] IN (
SELECT b.[ID] FROM tbl2 b)) then 1 else 0 end) AS ENR2 FROM tbl1 p
WHERE p.[Code] IN
(xxxx)
GROUP BY p.[Code]
ORDER BY p.[Code]
然后得到一个error message:
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an
aggregate or a subquery
google了一下这个message,说是：
you cannot use subquery when |
|
B*****g
发帖数: 34098 | 2 "you cannot use subquery when you use SUM function. Use Join and filter the
records。"
人家不是说了用join吗？
SELECT p.[Code],
COUNT(*) AS leads,
SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
SUM(CASE WHEN p.[Stage] ='ENR' AND b.[ID] IS NOT NULL THEN 1 ELSE 0
END) AS ENR2
FROM tbl1 p LEFT JOIN tbl2 b ON p.[ID] = b.[ID]
GROUP BY p.[Code] |
|
c*****d
发帖数: 6045 | 3 没看懂，你是想说subquery
(select C2 from T2) |
|
t****n
发帖数: 10724 | 4 wrong!
the subquery should read like this:
SELECT C2 FROM T2.
Since C2 is a column name not a table name
SELECT C1
FROM T1
WHERE C1 IN
(SELECT C2 FROM T2)
or you can use join
SELECT T1.C1
FROM T1 inner join T2 on T1.C1 = T2.C2 |
|
z**********8
发帖数: 2049 | 5 table1, employeeid, firstname, lastname
table2, employeeid, phone#, mailingaddress
talbe3, employeeid, saleamount
result set,
employeeid, firstname,lastname, phone#, mailingaddress,saleamount(>10000)
这个应该是典型的correlated subquery的例子吧。 |
|
P********R
发帖数: 1691 | 6 subquery里要有WHERE才能体现correlated吧？
（SELECT C2 FROM T2 WHERE C2＝C1）？ |
|
o******n
发帖数: 511 | 7 这个表长这样：
client site month visit_count uu
2 1 2011-01-01 1 300
2 1 2011-01-01 1 100
2 1 2011-01-01 1 100
2 2 2011-01-01 2 150
2 2 2011-01-01 2 50
2 3 2011-01-01 2 50
2 2 2011-01-01 3 400
2 3 2011-01-01 3 120
2 3 2011-01-01 3 120
2 3 2011-01-01 4 420
uu是unique users
每个client有好几个site，每个site每个月里，visit_count从1到很多，每个visit_
count对应一个uu数
有两句sql query计算每个visit_count对应的uu数和pe... 阅读全帖 |
|
w******i
发帖数: 1476 | 8 error message: invalid operation
我做了一个简单query, 数据是based on以前两个queries(subqueries)的results. 格式
很简单, 大概是:
SELECT SUBQUERYTABLE1.*, SUBQUERYTABLE2.*
FROM SUBQUERYTABLE1 LEFT JOIN SUBQUERYTABLE2
ON SUBQUERYTABLE1.PERSON_ID=SUBQUERYTABLE1.PERSON_ID;
前两天这个query一直都run得好好的, 今天在其中一个subquery里加了一个field,
subqueries都正常运行, 可是最后一个query却出现"Invalid Operation"
VB有check过,都有必需的references...
希望我把事情说清楚了. 到底是什么原因? 有没有人知道? 万分感谢! |
|
c*****d
发帖数: 6045 | 9 I do not know your exact requirements
but I guess you are required not to use subquery for performance reason
e.g.
select userid from t1 where userid not in (select userid from t2)
the above statement is resource-costing
But my answer is much efficient even if it is kind of subquery
do not use subqueries
on |
|
v*****r
发帖数: 1119 | 10 不是说非要用 having, LZ 的 concern 是能否避免 subquery, 不管用aggregate 或
analytic function，他提的问题无法避免 subquery, aggregate/analytic function
都必须要用在 subquery 里。
Vis |
|
O***T
发帖数: 124 | 11 In your second query, the subquery
SELECT nt.* FROM nt t, nt nt WHERE nt.x = t.x AND nt.y > t.y
doesn't have any join with you main query, alias nt only refer to the table
in your subquery.
It can be rewrite to SELECT a.* FROM nt t, nt a WHERE a.x = t.x AND a.y > t.
y
In this way, it is very obvious there is no join between your subquery with
the main one. Exist function here only plays a role like checking if the
statement is correct.
So be careful with the alias. Don't use the same one. It wil... 阅读全帖 |
|
w***y
发帖数: 6251 | 12 data science interview那本书里的
一个SQL题目，给一个‘logs’ table， 里面的数据是timestamp和jobID，譬如
timestamp jobID
1 1
2 2
3 1
4 1
5 3
6 2
7 1
8 4
要求是find all the jobIDs that appear at least twice consecutively. ‘
consecutively’是timestamp连续的意思，这个例子里找到的结果就是1
答案提到用variable做是O(n)，但是看不懂答案，完全不会用sql的variable的语法啊
，有什么好的tutorial网页推荐嘛？ 也不理解为什么这样子是O(n), 是不是因为
subquery不需要做join？
用local varia... 阅读全帖 |
|
l******g
发帖数: 6771 | 13 1. 两表结构并不完全相同
well, we have to assume everything you need for insert could be found from
f2, right? otherwise, you are missing data source anyway.
if that's true, then you can get from f2 section in the subquery, right?
2. 想用主键来判断
that's what you do in the subquery? 主键is primary key(s), right?
for quick example:
insert into t1
select fields from f2 from
(
t2 outer join t1 by key
minus
t2 equal join t1 by key
)
In short, you need to think about mathematics matirx before detailed
implementation wi... 阅读全帖 |
|
q*****e
发帖数: 6 | 14 EXISTS是先evaluate main query,在发现有match的时候在运行subquery.但是我感觉没有IN快啊，因为IN的subquery只要执行一次，然后建立一个index到那个temp table就行了。谁给说说理由啊。 |
|
l******n
发帖数: 9344 | 15 this is an interview question.
select the user names which are in table 1 but not in table 2. Do not us
e minus or any subquery
I think you answer still has subquery. |
|
B*****g
发帖数: 34098 | 16 请看附件。谢谢
TAB_A 8M records
TAB_B 0.2M records
subquery TAB_B will return 1-5 rows for most of records in TAB_B(except
around 20).
现在我要用 IN，即使sub里面只有一个return，至少１０分钟还没有结束。要是用 =，
只要几秒钟。试过各种hint，都没能成功。暂时我把SQL改成了PL／SQL（loop
subquery 里面），想知道只用SQL砸搞。
Note： 由于公司政策，table／column名字不能公开。 |
|
M*****R
发帖数: 650 | 17 多谢多谢。我就是想避免subquery，我在maintain一个query generation code logic
，现在
的设计不支持subquery。
看来是一定要改design了。
但是学到了OVER,也是很有用的，因为我的count要在不同的层上计算。我发现这个OVER
很有意义。
function |
|
S**H
发帖数: 1256 | 18 query1:
ID NOT=( sub query)
query2:
ID NOT IN (sub query)
两个subquery 都是一样的. 为什么query1 返回的值没有完全过滤掉subquery的ID.
NOT= , NOT IN 有啥区别?
谢谢!!! |
|
y****w
发帖数: 3747 | 19 nice。
有些是不可能兼顾的。比如最后一条就经常成为性能杀手。不过对dba script来说，这个就又非常实用，单条性能就是慢上三五秒也不会让我发狂。
大家讨论一下，看看有没有什么更好办法， SUBQUERY放到CTE或者TEMP TABLE里，
--------这个现有的dbms解决的都不太好，多层查询中间结果的join经常会是性能瓶颈
。 我做过些简单的尝试，以函数封装subquery，借助temp table实现indexed CTE。效
果还不错，限制也很多，只能用于比较简单的情况。dbms厂商在sql compiler里加上
index hint要容易的多。 |
|
l******y
发帖数: 60 | 20 有两个table: AAA, BBB，两个table共有的column有start_int, end_int,其中end_int
比start_int大，AAA表中的start_int到end_int的跨度比BBB表更大，现在要从BBB中
挑出所有AAA表中ID=12345的row 中的start_int 和end_int。我用了一个subquery:
select * from AAA where id=12345. 这一步从AAA中大概挑出2000行左右，但BBB很大
，有3千多万行
我的query是：
select start_int, end_int from BBB
where
exists (select * from
(select * from AAA where id=12345)temp
where temp.start_int <= BBB.start_int and temp.end_int>=BBB.end_int);
比方说：
subquery 中一行为：
start_int end_int
1 10
那么BBB中的以下列都要选出：
... 阅读全帖 |
|
s**********o
发帖数: 14359 | 21 我倒，你这是啥SUBQUERY啊，你的1，2不是一样的吗，你自己去研究一下这些概念吧
1. SUBQUERY
2. TEMP TABLE
3. TABLE VARIABLE
3. CTE |
|
p********n
发帖数: 11 | 22 Assuming no tie in salary in each department:
The subquery finds how many ppl have higher salary, and the guy has most
salary will not have a record returned from the subquery.
select S.*
from Salary S
left join
(
select S1.DepartmentID, S1.EmployeeID, count(*) as Cnt
from Salary as S1 join Salary S2
on S1.DepartmentID = S2.DepartmentID
and S1.EmployeeID <> S2.EmployeeID
and S1.Salary < S2.Salary
group by S1.DepartmentID, S1.EmployeeID
) as Ranking
on S.DepartmentID = Ranking.DepartmentID
... 阅读全帖 |
|
p********n
发帖数: 11 | 23 Assuming no tie in salary in each department:
The subquery finds how many ppl have higher salary, and the guy has most
salary will not have a record returned from the subquery.
select S.*
from Salary S
left join
(
select S1.DepartmentID, S1.EmployeeID, count(*) as Cnt
from Salary as S1 join Salary S2
on S1.DepartmentID = S2.DepartmentID
and S1.EmployeeID <> S2.EmployeeID
and S1.Salary < S2.Salary
group by S1.DepartmentID, S1.EmployeeID
) as Ranking
on S.DepartmentID = Ranking.DepartmentID
... 阅读全帖 |
|
d***e
发帖数: 793 | 24 面试的时候被问到过correlatd subquery的问题。可能这个跟query engine的design有
关系。通常来说第一种属于correlated subquery，是属于比较慢的情况。不过MySQL好
像对这种value in 的情况有optimization，所以可能第一个的性能应该也不错。如果
可以的话，尽量用第二种方法写。 |
|
A*******s
发帖数: 3942 | 25 data test;
input X $ Y;
cards;
a 34
a 45
a 7
a 12
a 11
b 1
b 56
b 66
c 19
c 43
c 12
c 71
;
run;
可以用number option产生行号，但是不能改column name。用monotonic()的结果很混
乱，不大清楚这个函数的机制是什么。
proc sql number;
select monotonic() as Num, X, mean(Y) as mean
from test
group by X;
quit;
我也尝试了用subquery来搞，但是结果还是一样。
proc sql number;
select monotonic() as Num, * from
(select X, mean(Y) as mean
from test
group by X);
quit;
唯一行得通的方法是先create table as，再用monotonic()。那为啥subquery不行呢？
proc sql;
create |
|
o******y
发帖数: 13 | 26 这是一道面试题。
you have three tables: students(sid, sname), courses(cid,cname), enrollment(
sid,cid).
find the number of students who didn't take 'Math'.
One solution:
select count(distinct sid)
from student
when sid not in
(select sid
from enrollment E, courses C
where E.cid = C.cid
and C.cname = 'Math')
Now the question is to find another solution without subquery, only using
joins. |
|
d**e
发帖数: 6098 | 27 但最后还是一个subquery
select count(*)
from
(....
minus
...
); |
|
o******y
发帖数: 13 | 28 Solution 1 是我interview时的回答。有问题吗？面试的人说是对的。他让我再给一个
只用joins,没有subquery的答案。我用left join做的，他说有问题。所以上来问高手:) |
|
o******y
发帖数: 13 | 29 can you use a subquery before IN? I think you can only have column naces
before IN. |
|
t*****e
发帖数: 1700 | 30 If the system doesn't support distinct count, do the following subquery
Select userid, count(productid) as ProdCount from
(select distinct userid, productid from Table)
group by userid
Q909) |
|
l****3
发帖数: 8 | 31 if subquery is allowed, maybe can do it like:
select T1.x x1,(select max(T2.x) from T T2 where T2.x
orderby x1; |
|
S*******0
发帖数: 198 | 32 1. Deadlock describes a situation that two or more threads (processes) are
blocked forever, waiting for each other.
Causes: one thread needs to visit the resource that another thread is
possessing and vice versa.
Effects: If deadlock happens, the threads involved will hang there forever
in an undesired status. Deadlock should be avoided.
2.
public ArrayList getToyotas(ArrayList cars)
{
if(cars == null) return null;
ArrayList toyotas = new ArrayList();
for ... 阅读全帖 |
|
|
c*****d
发帖数: 6045 | 34 -- not exists的subquery不要alias
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
)
) as t1, table3
where table3.id = t1.id |
|
w***y
发帖数: 6251 | 35 多谢！书里也给了这个用join的答案，不过说join是O(n^2)
用local variable是 O(n)
我对sql了解仅限于简单的select／join， 用variable的完全不懂 //汗
再问一下复杂度的问题，如果有的问题可以用subquery 避免join，是不是也是O(n)？ |
|
|
F**********I
发帖数: 19 | 37 Group meeting 08 视频剪辑
http://www.youtube.com/watch?v=P5JnisbHiRM
--*--2014--*--
Group Meeting 05 -----1/11/2014
• [Joe HR Pro English] Business Slang
• Problem Solving Steps
• Debug Tips: Find SQL Errors
• Business Email Tips
• Discuss a Job Description
Group Meeting 06 -----1/18/2014
• When to give table an alias in SQL
• Find SQL Error
• Real Case : Update a SQL Procedure used by SSRS report
• Laying the ground f... 阅读全帖 |
|
|
|
|
|
|
|
|
M*****R
发帖数: 650 | 45 这个肯定是可以的，我就是想知道有没有不用multiple query和subquery的可能性，
因为我的这个table是好几个join起来的。 |
|
s********o
发帖数: 861 | 46 没有不用multiple query和subquery的可能性。因为你一方面要一组一组地算（count
），一方面要一行一行地算（结果要每一个Session_ID），所以不可能一个简单join搞
定。 |
|
M*****R
发帖数: 650 | 47 可能真是如此。
麻烦呀，我是一个程序产生SQL，加一个subquery，编程复杂度高了好多。
count |
|
c*********n
发帖数: 1282 | 48 put the list in a table, then use subquery. |
|
|
F**********I
发帖数: 19 | 50 【周会Group meeting Topics】
时间：
每周六晚7：30PM Central Standard Time
周会内容：
SQL/SSRS/SSIS/SSAS/SharePoint/Power Excel Technical Topics,Tips&Tricks
行业经验分享，实战项目演示
面试经历分享，面试技巧，美国HR 专家英语交流，面试问答专题等
---------------*--2014--Mar--*-----------------
Group Meeting 14 -----3/29/2014
• Creating a Date Range Based on a Single Date Column
• Demo: SSRS Report with Date Range Parameter --Wei
• SSRS Report Types
• SQL & SSRS Class Q& A
• Interview Tips
Group Meeting 13 -----3... 阅读全帖 |
|
