M***7
发帖数: 2420 | 1 guys, thanks a lot .
Finally I used the following query
=====================
INSERT INTO NewTalbe
SELECT col1, col2 FROM TABLE1
UNION ALL
SELECT col3 col4 FROM TABLE2
======================
It woks.
If I use
=================
CREATE TABLE newtable AS
SELECT *
FROM (SELECT col1, col2 FROM TABLE1
UNION ALL
SELECT col3 col4 FROM TABLE2)
===================
It still did not work.
Could anyone explain it a little bit detail for me? I am really a rookie in
SQL.
Thanks. |
|
d*h
发帖数: 2347 | 2 select A.id1, A.id2, B.ColumnB1 ColumnB1, B.ColumnB2 ColumnB2, NULL as
ColumnC1, NULL as ColumnC2
from table1 A
right join table2 B
on A.id1=B.id1
and A.id2=B.id2
union all
select A.id1, A.id2, NULL as ColumnB1, NULL as ColumnB2, C.ColumnC1 ColumnC1
, C.ColumnC2 ColumnC2
from table1 A
right join table3 C
on A.id1=C.id1
and A.id2=C.id2; |
|
t****n
发帖数: 263 | 3 This looks like a bad database design to me.
But, anyway, I don't have to maintain this thing.
never used LINQ before. But using SQL, you can do this.
begin tran
select * from TABLE1 with (tablock, updlock, holdlock) where 1=2
then the TABLE1 will be locked until you commit or rollback the transaction. |
|
m******y
发帖数: 588 | 4 SQL SERVER 2000:
SELECT t1.A, t1.B
FROM table1 t1
LEFT OUTER JOIN table2 t3 ON t1.A=t3.A AND t1.B=t3.B
WHERE t3.A IS NULL
AND EXISTS (SELECT 1 FROM table2 t2 WHERE t2.A=t1.A)
2005:
SELECT A, B
FROM
(
SELECT A,B FROM table1 t1
EXCEPT
SELECT A,B FROM table2 t2
) t3
WHERE EXISTS (SELECT 1 FROM table2 WHERE t3.A=table2.A) |
|
e********g
发帖数: 2524 | 5 在mysql里面
有个table1:
|name |id|
|zhang|888|
|wang|666|
然后我想通过 table 1 创建另外两个table 分别叫zhang 和wang
我开始以为是
create table (select name from table1) (score int, grade char(2));
但是说我语法错了 |
|
g***l
发帖数: 18555 | 6 老SQL两步倒，
select zt, case course when 301 then score else null end as 301score,
case course when 302 then score else null end as 302score,
case course when 303 then score else null end as 303score
into table1
from table
select zt, max(301score) as 301, max(302score as 302, max(303score) as 303
from
table1
group by zt |
|
n********6
发帖数: 1511 | 7 current output:
product, counter
apple 2
apple 3
orange x
Objective:
product, counter
apple 5
orange x
table1
transactionid, product
1, apple
2, orange
3, apple
...
m, banana
table2
transactionid, product
1, peach
2, apple
3, apple
...
n, apple
current code (to be modified)
select product, count(transactionid)
from table1
group by product
union
select product, count(transactionid)
from table2
group by product |
|
B*****g
发帖数: 34098 | 8 叫你朋友参加CINAOUG 9月7号的活动
table1(id, col1)
table2(id, col2)
SELECT id, col1, null AS col2
from table1
union all
SELECT ID, null, col2
from table2 |
|
e*********y
发帖数: 29 | 9 比如说
table1
a,b,c,d
a,d,c,b
table2
a,b,c,d
a,d,c,b
当table1里头任意一个有update,table2也会跟随update.在没有Key的情况下,就是说不
能join两个表. 请问还有没有其它最优解?(除了删除Table2里头的然后在一笔一笔
insert) |
|
f******h
发帖数: 159 | 10 grant select,alter, update, delete, create on
databases1.table1 to user; 或者： grant all on
database1.table1 to user;
grant create, drop on database2 to user; |
|
g***l
发帖数: 18555 | 11 我还是没搞懂你想干什么，什么叫rank不同table里面的数据，你举个列子好不好，我
觉得你这个SP本身问题就很大，你有多少TABLE要RANK，RANK完了放在哪里？总不能就
扔在MANAGEMENT STUDIO里再COPY出来吧。为什么不能COMBINE呢
无非是这样么，RANK然后GROUP BY TABLENAME不就行了，这样就避免了有多少TABLE,
RANK出来放在哪里的问题。
TABLENAME DATA RANK
TABLE1 XXXX RANK
TABLE1 XXXX2 RANK
TABLE2 XXXX RANK
TABLE2 XXXX2 RANK |
|
k**g
发帖数: 1558 | 12 我要rank table1里的100 variables,结果存在table1里面，
接下来要rank table2里的同样的variables,结果存在table2里面，依次类推。。。 |
|
r*******n
发帖数: 3020 | 13 求指点。
我知道subquery 两种一种用 from table1， table2 where table1.id = table2.id；
第二种用 join；
第二种 performance比第一种要好。
还有用过with name as (select * from ...) 不知道算不算第三种 |
|
s**********o
发帖数: 14359 | 14 这两个有差异吗，看到有PROGRAMMER写了第一段，想改成第二段，就说不出为什么
Q1:
select
t1.*, t2.detail
from
table1 t1
left join
table2 t2 on t2.orderNo = t1.orderNo
Q2:
select
t1.*, t2.detail
from
table1 t1
left join
table2 t2 on t1.orderNo = t2.orderNo |
|
l******9
发帖数: 579 | 15 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: error of sql query in MS Access database
发信站: BBS 未名空间站 (Wed Oct 15 17:38:15 2014, 美东)
I need to do a sql query in MS Access 2012.
But I got error in MS Access:
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
) as t
) as t1, table3
where table3.id = t1.id
Syntax error: (missing operator) in query expression 'not exists ( ... ) as
t'
A... 阅读全帖 |
|
c*****d
发帖数: 6045 | 16 SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
)
) as t1, table3
where table3.id = t1.id |
|
x******m
发帖数: 736 | 17 table1
id col1 col2 col3...
table2
col_id col_name
1 col1
2 col2
3 col3
现在想用sql生成table3，
id col_name col_value
其中id，col_vaule来自于table1，col_name来自于table2.
多谢。 |
|
n***l
发帖数: 143 | 18 I am new too. Try use create view:
create view table3
select id, col_name, col-value
from table1
join table 2
where table1.id = table2.col_id |
|
s***c
发帖数: 1664 | 19 谢回复
应该是
update table1
set A=1
where A>=1
update table1
set B=1
where B>=1
...
写十几遍，不知道有没有快一点的办法 |
|
l********t
发帖数: 115 | 20 【 以下文字转载自 BuildingWeb 讨论区,原文如下 】
发信人: lindaghost (^_^), 信区: BuildingWeb
标 题: firefox problem with ASP.NET
发信站: Unknown Space - 未名空间 (Mon Mar 28 14:25:26 2005) WWW-POST
Table1.Rows[0].Cells[i].BorderColor=System.Drawing.Color.FromArgb(210,201,171)
;
Table1.Rows[0].Cells[i].BorderWidth=1;
firefox doesn't support the above code? it works fine with IE. |
|
w*s
发帖数: 7227 | 21 Hi in database i have Table1,
it has 2 fields, prevDay, currDay.
sometime the fields are null in database, not filled yet.
in the code i want to check if null or not 1st,
if ((DateTime)Table1.prevDay != (DateTime?)null)
when i run it, it says Nullable objects must have a value.
what's the correct way ?
also what's the difference between DateTime and DateTime?
thanks ! |
|
p*****g
发帖数: 445 | 22 我在code里面用了typedef定义以下
typedef state_t state_component[FC_MODES][TANK_LEVELS][BATT_LEVELS];
然后我用new新建两个指针
state_component* table1 = new state_component;
state_component* table2 = new state_component;
用vc++ 2005complie的时候报错
Error 30 error C2440: 'initializing' : cannot convert from 'state_t (*
)[256][256]' to 'state_component (*)'
但是当我把两个指针这样建立：
state_component* table1 = new state_component[1];
state_component* table2 = new state_component[1];
compile的时候就通过了。
望大牛出来传道授业解惑啊！ |
|
l******9
发帖数: 579 | 23 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: error of sql query in MS Access database
发信站: BBS 未名空间站 (Wed Oct 15 17:38:15 2014, 美东)
I need to do a sql query in MS Access 2012.
But I got error in MS Access:
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
) as t
) as t1, table3
where table3.id = t1.id
Syntax error: (missing operator) in query expression 'not exists ( ... ) as
t'
A... 阅读全帖 |
|
n*w
发帖数: 3393 | 24 没必要套这么多层。
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
)
) as t1, table3
where table3.id = t1.id |
|
N********n
发帖数: 8363 | 25
原文说ENTERPRISE MARKET图在TABLE1，图上也标明了是TABLE1。地球人都看
清楚了，偏偏你个NC看不懂还要出来大喊大叫, SB到家了。 |
|
c*********e
发帖数: 16335 | 26 (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) |
|
s*****t
发帖数: 119 | 27 下面两个HIVE query做同一件事情，请问那个query更efficient？假设在Map Reduce的
frame work 下
Query 1:
select id, count(distinct value) values
from table1
group by id;
Query 2:
select a.id, sum(1) values
from
(select distinct id, value
from table1
)a
group by a.id
;
另外请有哪本书或视频讲HIVE的query efficiency吗 |
|
k**g
发帖数: 1558 | 28 Thanks! If I have two existing tables, is this correct?
Proc SQL;
Create index Table1 date;
Create index Table2 date;
Create Table Table3 As
Select a.*, b.*
From Table1 a
Join Table2 b
On a.date=b.date; |
|
p********r
发帖数: 1465 | 29 我是想这样做：
1、有一个叫table的数据，我随机把他分成两个，table1和table2（table1包含80%
table的数据，table2包含剩余的20%）
2、用table2做一些运算，用proc iml，然后把运算结果输出到result里面。
我想把这个两个步骤循环做1000遍，这样我的result表里面就会出来1000个结果。
请问应该如何做会比较好？ |
|
p*******r
发帖数: 1951 | 30 table1是原始数据，table2是变量说明。
data _null_;
set table2 end=eof;
length temp $200.;
if type = 1 then temp=catx(" ",temp,var);
retain temp;
if eof then call symput('vars', temp);
run;
data pick(keep=id &vars);
set table1;
run; |
|
i******r
发帖数: 861 | 31 搞定了。用了3个Proc sql，先得到table1（ID,m1），再得到table2（ID,m2），最后
合并table1/table2。
有更好的办法吗？ |
|
s**********e
发帖数: 63 | 32 *Solved in SAS;
*Q1;
proc sql;
title1 "ID with average salary"
title2 ">department average";
select id, sal, avg(sal) as avgsalary format=dollar11.2;
from emp
group by department
having sal>avg(sal);
order by id;
quit;
*Q2;
proc sql;
create table lists as select vendor_id, count(good_id) as list from table1
group by vendor_id
quit;
proc sql;
select vendor_id from lists where list>(select list from lists where vendor_
id=002);
quit; |
|
s**********v
发帖数: 1379 | 33 select userid, productid, count(*)
from (
select distinct * from table1
)
group by productid
好久不写sql了，就是先去除duplicate然后group by ?
Q909) |
|
d**e
发帖数: 6098 | 34 select number_of_type_product_purchased, count(*) number_of_customer
from
(select userid, productid, count(*) number_of_type_product_purchased
from table1
group by userid, productid)
group by number_of_type_product_purchased
order by number_of_type_product_purchased, number_of_customer;
Q909) |
|
c*******e
发帖数: 8624 | 35 sel prod_cnt, count(1) from (
sel userid, count(distinct productid) as prod_cnt
from table1 group by 1) a
group by 1
order by 1 ;
Q909) |
|
i***e
发帖数: 3 | 36 select NoOfType as number_of_type_product_purchased, count(user) as number_
of_customer
from (
select userid, count(productid) as NoOfType
from (select distinct userid productid
from table1) a
group by userid) a1
group by NoOfType
order by number_of_type_product_purchased |
|
j****s
发帖数: 881 | 37 Select number_of_type_product_purchased , count (*) as number_of_customer
from
(select UserID, count (distinct ProductID) as number_of_type_product_
purchased from table1
Group by UserID) as table2
Group by number_of_type_product_purchased
Order by number_of_type_product_purchased
Go |
|
l*****a
发帖数: 559 | 38 SELECT `cnt` AS number_of_type_product_purchased, COUNT(`userid`) AS number_
of_customer FROM
(
SELECT `userid`,COUNT(`productid`) AS cnt FROM `table1`
GROUP BY `userid`
) TMP
GROUP BY `cnt` |
|
d**e
发帖数: 6098 | 39 create table thirdTable
as
select t1.col1, t2.col2
from table1 t1, table2 t2; |
|
u*****o
发帖数: 1224 | 40 最近在自学，想请教大家一个renaming的问题
用as可以实现两种renaming, rename一个column, 或者rename一个table
第一种：就是说比如我的table有个column A, 我把A的每个数×10000/2.5（想象一个
特别复杂的数学式，太复杂了以至于我必须rename成B）
然后我想select B-A>1的row
那我必须这么写：select A, A*1000/2.5 as B from...where A*1000/2.5 - A > 1
想用B代替calculated expression (select A, A*1000/2.5*800+6 as B from where B
-A>1) 就不work!!
第二种情况是rename一个table,
select T1.col1, T2.col2
from table1 as T1, table2 as T2 where...
这里rename table的话，我就可以用简写T1,T2出现在表达式里，甚至出现在renaming
的前面。。为什么第一个例子就不能用简写呢
汗，好难说清楚，不知有... 阅读全帖 |
|
N********g
发帖数: 132 | 41 3. there are different kind of databases; given a query, system will tell
you which database you should connect(system gives you a string like "Oracle
" or "MySQL"). Design a class that could handle any query.
这题考的是separation of interface and implementation，code against interface
，not against implementation，如果做过JDBC开发，立马就知道什么意思。
好比你写了一些DAO（Database access object），就是用来把数据从数据库里面读出
来，然后交给中间层处理的工具类，但是你们开发组在不久的将来，打算把数据库由
oracle转成mysql。如果这些DAO里面的具体选取数据的方法，都跟oracle数据库相关，
都跟oralce对应的java class相关，那转数据库的时... 阅读全帖 |
|
N********g
发帖数: 132 | 42 3. there are different kind of databases; given a query, system will tell
you which database you should connect(system gives you a string like "Oracle
" or "MySQL"). Design a class that could handle any query.
这题考的是separation of interface and implementation，code against interface
，not against implementation，如果做过JDBC开发，立马就知道什么意思。
好比你写了一些DAO（Database access object），就是用来把数据从数据库里面读出
来，然后交给中间层处理的工具类，但是你们开发组在不久的将来，打算把数据库由
oracle转成mysql。如果这些DAO里面的具体选取数据的方法，都跟oracle数据库相关，
都跟oralce对应的java class相关，那转数据库的时... 阅读全帖 |
|
F*******2
发帖数: 371 | 43 还有看table1和table2中ID1 ID2 的format是否一致 |
|
l******9
发帖数: 579 | 44 thanks for you reply, I have updated my post.
i need to do
sum (table1.VALUE * table3.VALUE) AS new_value
I got error:
ERROR [HY000] ERROR: Attribute tt_b.new_value must be GROUPed or used
in an aggregate function
|
|
l******9
发帖数: 579 | 45 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL run a stored procedure by fetching from a cursor row by row
发信站: BBS 未名空间站 (Fri May 23 17:57:23 2014, 美东)
I need to run a stored procedure on SQL server 2008.
But, I can only fetch one row from the cursor. After that, the @@FETCH_
STATUS is -1.
DECLARE @my_table TABLE
(
code int not null
);
INSERT INTO @my_table (id)
SELECT DISTINCT a.id
FROM table1 as a
WHERE a.value = 'abc'
ORDER BY a.id A... 阅读全帖 |
|
m*****n
发帖数: 2152 | 46 select table1.address table2.id_num into new_table in DB
from
of |
|
l******9
发帖数: 579 | 47 On SQL Server 2008 R2, I need to add some columns into a table from another
table.
e.g.
Table1:
name value id
Jim 288 abf
Table 2:
name acct num rank
Jim 12 29 8
Jim 98 95 7
Jim 20 52 9
What I need:
name value id acct num rank acct num rank acct num rank
Jim 288 abf 12 29 8 98 95 7 20 52 9
The record numbers in table 2 may be variable.
Any help would be appreciate. |
|
c*****o
发帖数: 1702 | 48 table1的where clause的column name没有 |
|
d*********0
发帖数: 12 | 49 table1 和 table2 的schema必须一致, 如果用*, 两表结构可能(column)不一致,出错. |
|
s***y
发帖数: 904 | 50 select name, salary from
(select name, salary, (rank ()over (order by salary desc)) as rank) tabel1
where table1.rank=n |
|
