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! |
|
l******9
发帖数: 579 | 3 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL copy a table into a new table and add a new column
发信站: BBS 未名空间站 (Fri May 23 12:05:22 2014, 美东)
need to copy a table into a new table on SQL server 2008. Also, add a new
column into the new table.
The values of the new column depends on the compare result between the new
table and another table.
Example,
Table1:
col1 col2 col3
abc 346 6546
hth 549 974
Table1_new:
col1 col2 col3 c... 阅读全帖 |
|
l******9
发帖数: 579 | 4 need to copy a table into a new table on SQL server 2008. Also, add a new
column into the new table.
The values of the new column depends on the compare result between the new
table and another table.
Example,
Table1:
col1 col2 col3
abc 346 6546
hth 549 974
Table1_new:
col1 col2 col3 col4
abc 346 6546 1
hth 549 974 0
Table2:
col1
abc
sfsdf
If Table2's col1 appear in Table1 col1, mark col4 as 1 in Table1_new, el... 阅读全帖 |
|
l******9
发帖数: 579 | 5 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL copy a table into a new table and add a new column
发信站: BBS 未名空间站 (Fri May 23 12:05:22 2014, 美东)
need to copy a table into a new table on SQL server 2008. Also, add a new
column into the new table.
The values of the new column depends on the compare result between the new
table and another table.
Example,
Table1:
col1 col2 col3
abc 346 6546
hth 549 974
Table1_new:
col1 col2 col3 c... 阅读全帖 |
|
l******9
发帖数: 579 | 6 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL copy a table into a new table and add a new column
发信站: BBS 未名空间站 (Fri May 23 12:05:22 2014, 美东)
need to copy a table into a new table on SQL server 2008. Also, add a new
column into the new table.
The values of the new column depends on the compare result between the new
table and another table.
Example,
Table1:
col1 col2 col3
abc 346 6546
hth 549 974
Table1_new:
col1 col2 col3 c... 阅读全帖 |
|
l******y
发帖数: 60 | 7 问个问题
我有如下table:
col1 col2 col3
A S 4
A T 12
A U 14
A V 6
A W 9
A X 17
A Y 23
A Z 346
B S 45
B T 34
B U 34
B V 56
B W 67
B X 342
B Y 23
B Z 1
C S 34
C T 78
C U 3
C V 8
C W 34
C X 6
C Y 7
C Z 100
对于col1 中的每个值，要找出col3值最小的一列。即如下结果：
col1 col2 col3
A S 4
B Z 1
C U 3
Table很大，每一个distinct col1 都有80万个col2与之对应，即： select count(*)
wher... 阅读全帖 |
|
i*****w
发帖数: 75 | 8 Method 1: Row_Number, Partition by
Method 2: Assume there are no duplicated records for each group so Col1 and
Col3 are unique:
Declare @tbl Table (col1 varchar(10), col2 varchar(10), col3 int)
INSERT INTO @tbl (col1, col2, col3)
SELECT 'A', 'S', 4 UNION ALL
SELECT 'A', 'T', 12 UNION ALL
SELECT 'A', 'U', 14 UNION ALL
SELECT 'A', 'V', 6 UNION ALL
SELECT 'A', 'W', 9 UNION ALL
SELECT 'A', 'X', 17 UNION ALL
SELECT 'A', 'Y', 23 UNION ALL
SELECT 'A', ... 阅读全帖 |
|
l******9
发帖数: 579 | 9 I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
col1 INT
col2 INT
col3 INT
col4 DOUBLE PRECISION
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
Table1 is :
col1 col2 col3 col4
80 790 3498 18654.064361
81 589 3182 2138518.05404
80 ... 阅读全帖 |
|
l******9
发帖数: 579 | 10 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: sort two same tables SQL but different results
发信站: BBS 未名空间站 (Fri May 9 09:57:41 2014, 美东)
I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
T... 阅读全帖 |
|
s*******n
发帖数: 4402 | 11 楼上几位都是高手，给出的解比较复杂，我给一个简单的：
先建一个#table, 原来的表是tb
create table #table (col1 char(1), col2 char(1), col3 int)
insert into #table(col1) (select distinct col1 from tb)
update #table set col3=c.min_col3
from #table as t
join (select min_col3=min(col3), col1 from tb
) as c on t.col1=c.col3
update #table set col2=t.col2
from #table as t join tb as b on t.col1=b.col1 and t.col3=b.col3
不管你原来数据有没有重复的，你就是要最小的那一行，这个可以达到要求。 |
|
l******9
发帖数: 579 | 12 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: sort two same tables SQL but different results
发信站: BBS 未名空间站 (Fri May 9 09:57:41 2014, 美东)
I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
T... 阅读全帖 |
|
l******9
发帖数: 579 | 13 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: sort two same tables SQL but different results
发信站: BBS 未名空间站 (Fri May 9 09:57:41 2014, 美东)
I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
T... 阅读全帖 |
|
w*r
发帖数: 2421 | 14 来讨论一下join index的用法，说说你们的经历，我先砸一块砖，V2R6.1的optimizer
有问题
我有一个commonly used join(为了简便，很多syntax 的细节错误就不管了）,
Table A Inner Join B on
a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3
and a.start_date <= b.start_date and a.end_date >= b.end_date
Table A 和 B分别对这些join的col都有hash index.
我建立了一个A $ B 的JI
create A_B_JI as
select a.col1, b.col1, a.col2, b.col2 , a.col3, b.col3 a.started_date
a.rowid, b.rowid
from a join b
on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3
and a.start_date <= b.start_date an |
|
B*****g
发帖数: 34098 | 15 seems (1=3) > 2 > 4, why? how can 1=3? 谁给说说?
Thanks
col0 is unique
DELETE FROM table_name A
WHERE A.col0> ANY (SELECT B.col0 FROM table_name B
WHERE A.col1 = B.col1 AND A.col2 = B.col2)
DELETE FROM table_name A
WHERE A.col0 > (SELECT MIN(B.col0) FROM table_name B
WHERE A.col1 = B.col1 AND A.col2 = B.col2)
DELETE FROM table_name A
WHERE EXSITS (SELECT 1 FROM table_name B
WHERE A.col1 = B.col1 AND A.col2 = B.col2 AND A.col0 > B.col0
)
DELETE FROM |
|
M*********e
发帖数: 190 | 16 Don't know TSQL.
In Oracle, use rowid pseudocolumn.
思想就是先找到有discint col1的所有行（得到set 1）和有distict col2的所有行(得
到set 2)。取 set 1和 set 2中rowid相同的行.
不知道有没有漏洞。
#################
create table test(col1 varchar2(5), col2 varchar2(5));
insert into test values ('V1','B');
insert into test values ('V12','F');
insert into test values ('V3','F');
insert into test values ('V2','C');
insert into test values ('V2','D');
insert into test values ('V3','E');
#################
select * from test where rowid i... 阅读全帖 |
|
M*********e
发帖数: 190 | 17 or use a cross join.
select * from test where (col1, col2) in
(
select * from
(
(select col1 from test where col1 not in
(select col1 from test group by col1 having count(*)>1 ))
cross join
(select col2 from test where col2 not in
(select col2 from test group by col2 having count(*)>1 ))
)
);
COL1 COL2 |
|
c*****d
发帖数: 6045 | 18 select a.col1, a.col2, b.col3
from a,b
where a.col1 = b.col1
and a.col2 = a1
union all
select a.col1, a.col2, c.col5
from a,c
where a.col1 = c.col1
and a.col2 <> a1 |
|
c*******r
发帖数: 3289 | 19 col1, col2两个column存放人名id, 要求给出任意一个人的id,找出所有和这个人有联
系的人，以及所有其他有联系的人。例如
col1 col2
---- ----
A B
A C
B J
M B
X J
C K
Q A
输入的人名是A, 需要返回
1. COL2 WHERE COL1 = A，输出B和C；
2. COL1 WHERE COL2 = A, 结果加入Q;
3. COL2 WHERE COL1 IN (B,C,Q), 加入结果;
4. COL1 WHERE COL2 IN (B,C,Q)，加入结果；
...
如此类推直至找不到新的ID
这似乎是个递归问题，能写个store procedure吗 |
|
n****t
发帖数: 182 | 20 Why not use data step?
proc sql;
select col1, col2, count(*) as count, 'N' as dup from a group by col1,
col2 having count=1
union select distinct col1, col2, count(*) as count, '
N' as dup from a group by col1, col2 having count>1
union select col1, col2, count(*) as count, 'Y'
as dup from a group by col1, col2 having count>1; |
|
a*********u
发帖数: 1463 | 21 有两个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里
请问还有什么简单易操作的方法吗
谢谢 |
|
S*****0
发帖数: 538 | 22 这个没问题，刚试过。 估计,是#test table 需要先定义，不然，ＳＱＬ Server
paser 会遇到SELECT ...INTO #test 就定义一次＃test, 导致错误 。
IF OBJECT_ID(N'tempdb.dbo.#test') IS NOT NULL DROP TABLE #test
CREATE TABLE #test(col1 int)
IF (2>1)
INSERT INTO #test(col1)
SELECT 1 as col1
ELSE
BEGIN
INSERT INTO #test(col1)
SELECT 2 as col1
END |
|
v*****r
发帖数: 1119 | 23 嗯，很好，就有点 double-negative，俺加一个茴的写法 (natual join):
select t1.*
from (select * from test where col1 in (select col1 from test group by
col1 having count(*)=1)) t1,
(select * from test where col2 in (select col2 from test group by
col2 having count(*)=1)) t2
where t1.col1 = t2.col1 |
|
j****s
发帖数: 881 | 24 多谢上面几位解释，尤其是北京姐姐，把题目的意思讲清楚了。
另一种写法:
select col1, col2 from table
where clo1 not in
(select col1, count(col2) from table
group by clo1
having count(col2)>1
Intersect
select col1, col2 from table
select col2, count(col1) from table
group by clo2
having count(col1)>1)
记下IDs，发包子去了。 |
|
B*****g
发帖数: 34098 | 25 其实想一想B-tree的工作原理就明白了
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.
1
11.2.3.3.1 When the Optimizer Uses Index Range Scans
The optimizer uses a range scan when it finds one or more leading columns of
an index specified in conditions, such as the following:
col1 = :b1
col1 < :b1
col1 > :b1
AND combination of the preceding conditions for leading columns in the index
col1 like 'ASD%' wild-card searches should not be in a leading position othe
rwise the condition col1 like '%ASD' do... 阅读全帖 |
|
e*********y
发帖数: 29 | 26 table A
Id,col1,col2,col3
1 a b c
2 c e f
3 a b c
4 a b c
table B
col1,col2,col3,Id
aa bb cc 1
cc bb dd 1
ee ff gg 1
ff aa ee 2
aa bb cc 2
bb ee ff 4
cc bb dd 4
ee ff gg 3
table c:
col1,col1,col2,col3, count
1 aa bb cc 2
2 cc bb dd 2
3 ee ff gg 2
4 ff aa ee 1
5 bb ee ff 1
现在有table A, table B, 和 table c
有没有办法不用每次run Inquery,而直接得到table c 的结果.
就是说table C的结... 阅读全帖 |
|
d**e
发帖数: 6098 | 27 In oracle, say select max,
col1 col2 col3
--------------------
a b 1
a b 2
c d 4
c d
-------------------
group by col1, col2, i want something like a/b will return 2, c/d return
null, since c/d has a null value.
"select col1, col2, max(col3) from table group by col1, col2" doesn't work.
Is ia any way to make the max function return null if there is a null value
in the column?
Thanks! |
|
y*****g
发帖数: 677 | 28 My solution is only work for fixed number of values in the col2,
for simplicity, if there is only 2 values in col2,
the following works:
insert into combined select a.col1, substring_index(a.col2,',',1) from
mytest a union all select b.col1,substring_index(b.col2,',',-1) from mytest
b;
mysql> select * from newt order by col1;
+------+-------------------------------+
| col1 | substring_index(a.col2,',',1) |
+------+-------------------------------+
| a1 | b11 |
| a1 ... 阅读全帖 |
|
i*****w
发帖数: 75 | 29 建议一种方法:
1) UNPIVOT TableA, You will get:
ID1, COL1, CONTENT1
ID2, COL2, CONTENT2
ID3, COL3, CONTENT3
2) Join TableA and TableB by LIKE.
EXAMPLE:
-- Prepare Source Table
DECLARE @tblA Table (ID int identity, col1 varchar(100), col2 varchar(100),
col3 varchar(100))
INSERT INTO @tblA (col1, col2, col3)
SELECT 'this is a test', 'I am not sure', 'Give it a try.'
UNION ALL
SELECT 'who cares', 'No one knows', 'Why not'
UNION ALL
SELECT 'it is impossible', 'please let me know', 'be honest'
-- ... 阅读全帖 |
|
c*****d
发帖数: 6045 | 30 和其他字段无关，多半是col1字段类型不同
试着只对col1排序，并且转换成数值
oracle:
SELECT *
FROM table2 t2
ORDER BY to_number(t2.col1) asc
sql server:
SELECT *
FROM table2 t2
ORDER BY cast(t2.col1 as int) asc |
|
a**d
发帖数: 4285 | 31 执行了一下，可行：
select t1.col1, t1.col2, t1.col3,
col4= case when t2.col1 is not null then 1
else 0
end
into table_new
from table1 t1 left join table2 t2 on t1.col1=t2.col1 |
|
s**********o
发帖数: 14359 | 32 你的TABLE1的各个COLUMN NAME是什么呢，我觉得TABLE1本身就有问题
TABLE 1，如果是这样一个TABLE，本身就没有意义
ID COL1 COL2 COL3
1 小甲 老乙 大丙
2 小A 老B 大C
3 张三 李四 王二麻子
显然，虽然叫COL1, COL2. COL3，但肯定是有些联系，其实是这样的
COL1=学生
COL2=老师
COL3=家长
其实TABLE1应该是这样
ID 学生 老师 家长
1 小甲 老乙 大丙
2 小A 老B 大C
3 张三 李四 王二麻子
如果TABLE1建好的话，TABLE2和TABLE3都没什么意义，如果你是CLEANUP
直接SELECT COL1 AS ID1, COL2 AS ID2, COL3AS ID3 FROM TABLE1
因为是DDL,你的COLUMN数肯定是一定的，不可能是N个,只能一一列举
不过合并后的TABLE3还是没什么意义，因为ID1 ID2本身... 阅读全帖 |
|
w****w
发帖数: 521 | 33 Something like:
with friend_cte as (
select col1 as col from friend where col2='a'
union
select col2 as col from friend where col1='a'
union
select col1 as col from friend join friend_cte
on friend.col2=friend_cte.col
union
select col2 as col from friend join friend_cte
on friend.col1=friend_cte.col
);
select col from friend_cte; |
|
c**t
发帖数: 2744 | 34 Can any nHibernate expert let me know what's wrong with the following? I
alwasy got Acccount.ListAttr as null; when do "foreach(var a in Account.
ListAttr)" I got invalid cast error: can't cast ISet to IList..
I have Account.hbm.xml as follows:
...
..
and Account.cs has:
public virtual ISet ListAttr {get; set; }
..
Also have SomeOtherClass.hbm.xml:
...
阅读全帖 |
|
c*********e
发帖数: 16335 | 35 (select col1,col2,col3 from table1
except
select col1,col2,col3 from table2)
union
(select col1,col2,col3 from table2
except
select col1,col2,col3 from table1) |
|
o******6
发帖数: 538 | 36 ☆─────────────────────────────────────☆
cyear (cyear) 于 (Wed May 14 23:40:09 2008) 提到:
有个文件：
A 4
A 5
B 1
B 2
B 3
怎么能得到：
A 9
B 6
谢谢！！
☆─────────────────────────────────────☆
cyear (cyear) 于 (Wed May 14 23:59:31 2008) 提到:
哪位帮帮忙！1
☆─────────────────────────────────────☆
GunS (火枪) 于 (Thu May 15 08:24:25 2008) 提到:
proc sort data=a;
by col1;
run;
data b;
set a;
by col1;
if first.col1 then col3=0;
col3+col2;
if last.col1;
drop col2;
rename col3=col2;
run;
有个文件：
A 4
A 5
B |
|
o****o
发帖数: 8077 | 37 data _xxx;
input var1 var2 var3 var4;
cards;
2 4 6 7
4 9 7 6
5 2 1 1
7 3 7 3
;
run;
proc transpose data=_xxx out=_xxx2;
run;
proc means data=_xxx2 noprint;
var col1-col4;
output out=_xxx3(keep=v1-v4)
maxid(col1(_name_)
col2(_name_)
col3(_name_)
col4(_NAME_))= v1-v4/autoname;
run;
proc transpose data=_xxx3 out=_xxx3t;
var v1-v4;
run;
d... 阅读全帖 |
|
l****u
发帖数: 529 | 38 select table1.*, case when table1.col1=table2.col1 then 1 else 0 end as col4
from table1 left join table2 on table1.col1=table2.col1; |
|
R*****d
发帖数: 420 | 39 我觉得用sql full outer join 也可以。
select a.col1, a.col2, b.col1, b.col2 from tableA a full outer join tableB b
on a.col1=b.col1; something like this.
sas sql should have it. |
|
p***n
发帖数: 635 | 40 Here is the submit button which will select all values in your select box
submits the form (it is the equivalent of user selecting all values.
The java script function used by the onclick button.
|
|
