v*****r
发帖数: 1119 | 1 Oracle's traditional non-ANSI standard left outer join query, your query is
equivalent to the following two queries if using ANSI stardard:
1. select tbl1.xx, tbl2.xx
from tbl1 LEFT OUTER JOIN tbl2
on tbl1.col1=tbl2.col2;
or
2. select tbl1.xx, tbl2.xx
from tbl2 RIGHT OUTER JOIN tbl1
on tbl2.col2=tbl1.col1; |
|
t*********i
发帖数: 217 | 2 Any one knows what this means?
select tbl1.xx, tbl2.xx from tbl1, tbl2 where tbl1.col1=tbl2.col2 (+)
many thanks! |
|
t*********i
发帖数: 217 | 3 I see. Thanks a lot.
The syntax is a little strange. tbl1 get all records when tbl2 has a (+)
there. |
|
y**b
发帖数: 88 | 4 found one hack on someone's blog.
(1) create the table (tbl1) without index, then load the data
(2) create another empty table (tbl2) which has same structure
as tbl1, plus index
(3) stop mysql server
(4) copy tbl2.frm, tbl2.MYI to tbl1.frm, tbl1.MYI
(5) myisamchk -r -q tbl1.MYI
Since it needs to stop mysql start, it won't work for us.
took
Google
best |
|
M*****r
发帖数: 1536 | 5 select * from tbl1 except (select tbl1.* from tbl1, tbl2 where tbl1.A=tbl2.A
and tbl1.B=tbl2.B)
上面这个query基于一些假设，也许只适用于你给的例子 |
|
e****7
发帖数: 4387 | 6 insert into tbl1
select tbl2.*
from tbl2 left outer join tbl1
on tbl2.pk = tbl1.pk
where tbl1.pk is null |
|
a*********u
发帖数: 1463 | 7 有两个sql server table
table1 (col1, col2, col3 ... col9)
table2 (cola, colb, colc)
目的是弄一个 excel table3
包含col1， col2, col3, col5, col6, colc
colc的值是当tbl1.col1=tbl2.cola and tbl1.col2 = tbl2.colb
我现在的想法是
select col1,col2,col3, col5, col6, 0 as colc from tbl1 into temp_tbl1
update temp_tbl1
set temp_tbl1.colc = tbl2.colc
from temp_tbl1
inner join tbl2
on (temp_tbl1.col1 = tbl2.cola and temp_tbl1.col2 = tbl2.colb)
然后再用ssis 把数据弄到excel里
请问还有什么简单易操作的方法吗
谢谢 |
|
m**********2
发帖数: 2252 | 8 嗯。。稍微改了一下，OK啦。。
SELECT A.Product, A.Status, SUM(A.Cnt) AS StatusCnt
FROM (
SELECT CASE
WHEN A1.Product IS NULL THEN 0
ELSE 1
END AS Cnt,
B1.Product,
B1.Status
FROM tbl1 A1 RIGHT JOIN
(SELECT A2.Product, B2.Status
FROM (
(SELECT DISTINCT product FROM tbl1) A2
CROSS JOIN (SELECT DISTINCT status FROM tbl2) B2))B1 ON A1.Product = B1.
Product AND A1.Status = B1.Status) A
GROUP BY A.Product, A.Status
ORDER BY A.Product, A.Status |
|
X******2
发帖数: 5859 | 9 tmp = JOIN tbl1 BY id1, tbl2 BY id2;
假设tbl1和tbl2中相关column的名字为id1和id2. |
|
|
|
M*****r
发帖数: 1536 | 12 1. It seems if
grant all on db1.* to 'abc'@'%' identified by 'abc'
then, if I only want to revoke select from one table
revoke select on db1.tbl1 from 'abc'@'%'
it doesn't work?
2. If I drop an object (e.g. table), will the privileges granted to it be
dropped automatically?
Some old readings refer to these issue in 3.x/4.x days, have them been fixed on
5.0.x(seem doesn't work on 5.0.51a)? or will they be fixed later? |
|
m**********2
发帖数: 2252 | 13 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 |
|
c**t
发帖数: 2744 | 14 join tbl1 and tbl2 first, then sum |
|
m**********2
发帖数: 2252 | 15 但是join的话，
COUNT(*) AS leads,
SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1
这两个数就不对了，这两个只需要计tbl1里的。。。 |
|
B*****g
发帖数: 34098 | 16 "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] |
|
h******l
发帖数: 422 | 17 你的意思是:
假设 tbl1:
col1 col2 col3 col4 col5 col6 |
|
v*****r
发帖数: 1119 | 18 哈哈，如果你习惯了 +=， =+ syntax, 看 ANSI out join syntax 还觉得别扭哪。 |
|
s*******6
发帖数: 3 | 19 (+) is the best expression. ANSI out join is for some who is not a DB
developer. |
|
a9
发帖数: 21638 | 20 这个等于full outer join吗？
is |
|
|
v*****r
发帖数: 1119 | 22 To do "full outer join" using old oracle outer join syntax, you will need a
UNION to union left and right outer join.
Oracle old +=, =+ syntax in most cases are more concise than ANSI syntax,
but ANSI full outer join syntax seems more concise than += UNION =+ old
syntax. |
|
s*********e
发帖数: 1051 | 23 how to write the pig latin code to do below?
select * from tbl1 where id in (select id from tbl2);
thanks. |
|
s*********e
发帖数: 1051 | 24 【 以下文字转载自 Statistics 讨论区 】
发信人: statcompute (statcompute), 信区: Statistics
标 题: a question about pig latin
发信站: BBS 未名空间站 (Mon Feb 24 21:04:00 2014, 美东)
how to write the pig latin code to do below?
select * from tbl1 where id in (select id from tbl2);
thanks. |
|
