s*****n
发帖数: 2174 | 1 看到不同人写的sql语句用不用的join方法, 比如下面这两种写法:
SELECT
table1.id
FROM
table1
INNER JOIN table2 ON table1.id = table2.id
INNER JOIN table3 ON table1.id = table3.id
INNER JOIN table4 ON table1.id = table4.id
WHERE
table1.value > 0
AND table2.value > 0
AND table3.value > 0
AND table4.value > 0;
SELECT
table1.id
FROM
table1
INNER JOIN table2 ON table1.id = table2.id
AND table2.value > 0
INNER JOIN table3 ON table1.id = table3.id
AND table3.value > 0
INNER JOIN table4 ON table |
|
b*****e
发帖数: 364 | 2 Here is another example of such kind of subquary.
if object_id('##Table1')<>0 drop table ##Table1
Create table ##Table1 (
[Record Value] varchar(20), [Record Date] smalldatetime
)
go
insert into ##Table1 values ('Record 1','07/21/2003')
insert into ##Table1 values ('Record 2','07/22/2003')
insert into ##Table1 values ('Record 3','07/23/2003')
insert into ##Table1 values ('Record 4','07/24/2003')
insert into ##Table1 values ('Record 5','07/25/2003')
insert into ##Table1 values ('Record 6', |
|
l******9
发帖数: 579 | 3 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL combine two tables into one table and add a new column
发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
I need to combine two tables into one. Ans also, add a column (assign an int
value) to the new table on SQL. So that the rows from table1 and ones from
table2 are assigned with different values.
Example,
table1
ID1 ID2 ID3 VALUE
table2
ID1 ID2 ID3 VALUE
table3
ID1 ID2 ID3 VALUE
i need to combine ... 阅读全帖 |
|
f*****e
发帖数: 5177 | 4 I run SQL query estimated execution plan again one query.
The plan showed that the cost of "delete" and "insert" is 900%.
What does that mean?
PS: Which one of the following queries is better?
Query1:
if exist (select * from table1 where ...)
begin
update table1 set ...
end
else
begin
insert into table1 ...
end
Query2:
if exist (select * from table1 where ...)
begin
delete from table1 where ...
end
insert into table1 ...
Query3:
delete from table1 where ...
insert into table1 ... |
|
w*********g
发帖数: 2330 | 5 两个table, table1, table2 . 每个table各有两列A 和 B. 。 如何从table 1 选择出，
table1.A 和table2.A相等，在这个条件下，B不等的那些行。
想选择出如下的行
A B
835448 3
835448 4
835448 5
835449 2
835449 3
table1 的数据
A B
835448 0
835448 1
835448 2
835448 3
835448 4
835448 5
835449 0
835449 1
835449 2
835449 3
table2的数据
A B
835448 0
835448 1
835448 2
835449 0
835449 1
我写的语句
select distinct table1.A, table1.B
from table1, table2
where table1.A |
|
h****t
发帖数: 22 | 6
首先声明，我是作应用程序开发的，在这个版上贴query是在班门弄斧，让大家见笑了。
CREATE TABLE table1(id int,InheritedFromID int)
CREATE TABLE table2(id int,prop1 nvarchar(50),prop2 nvarchar(50))
CREATE TABLE table3(id int,prop1 nvarchar(50),prop2 nvarchar(50))
insert into table1 (id,InheritedFromID) values(1,0)
insert into table1 (id,InheritedFromID) values(2,1)
insert into table1 (id,InheritedFromID) values(3,2)
insert into table2(id,prop1,prop2) values(1,'a0','a1')
insert into table2(id,prop1,prop2) values(1,'a2','a3')
insert into... 阅读全帖 |
|
g***x
发帖数: 494 | 7 有两个表分别有1024和2048个0到4000的数（不一定是整数），这两个表都是递增的。
并且表里的值都是已知的，两个表的第一个元素都是0，最后一个元素都是4000例如
table1[0]=0;table1[1]=0.5;table1[2]=1; table1[3]=2; ...... table1[1023]=4000
table2[0]=0;table2[1]=0.2;table2[2]=0.5;table2[3]=0.75;table2[4]=1.1; table2
[5]=2; ... table2[2047]=4000
现在的问题是如何从table2中挑选出1024个index使得这1024个值所组成的曲线和
table1的曲线最好地一致。
比如上面的例子中可从表2中挑选出第 0，2，4,5,... 2047个元素得到的数是：
0，0.5，1.1，2，... 4000这和表1的元素0,0.5,1,2,4000符合的很好。
请问可以用什么算法来实现，并且最快。 |
|
x****e
发帖数: 55 | 8
MySQL
sort merge join正是我想要的，是否只有ORACLE才有这个命令？
结果是：
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE table1 index table1_index table1_index 9
47506 Using index
1 SIMPLE table2 ref table2_index table2_index 9 codis.
table1.id 1 Using where; Using index
似乎采用这个命令，速度非常快，也是不到1秒
这个跟 create table jointable select table1.id from table1, table2 where
table1.ID = table2.ID; 好像一样的快
但select table1.i |
|
p*c
发帖数: 421 | 9 declare @emp_code varchar(1000)
select @emp_code = COALESCE(@emp_code + ' ', '') + table2.emp_code
from table1 table1
LEFT JOIN table2 table2 ON table1.emp_id = table2.emp_id
where emp_id = 1
select @emp_code
this does return for Mary who is emp_id=1:
a b c
however, if i supply more emp_id in the where clause, for example:
declare @emp_code varchar(1000)
select @emp_code = COALESCE(@emp_code + ' ', '') + table2.emp_code
from table1 table1
LEFT JOIN table2 table2 ON table1.emp_id = table2.emp_id |
|
c*******e
发帖数: 8624 | 10 具体query;
select A, B from table1
minus
select A, B from table2
或者
select table1.A, table1.B
from table1
left outer join table2
on table1.A = table2.A
and table1.B = table2.B
where table2.B is null ; |
|
d*******n
发帖数: 109 | 11 create table table1 (id int , description varchar(20))
insert into table1 values (1, '0-2')
insert into table1 values (2, '2-5')
insert into table1 values (3, '5-10')
insert into table1 values (4, 'more than 10')
create table table2 (userid int, id int)
insert into table2 (userid, id) values (1,1)
insert into table2 (userid, id) values (2,NULL)
insert into table2 (userid, id) values (3,4)
insert into table2 (userid, id) values (4,3)
insert into table2 (userid, id) values (5,NULL)
insert into tab... 阅读全帖 |
|
l******9
发帖数: 579 | 12 【 以下文字转载自 Statistics 讨论区 】
发信人: light009 (light009), 信区: Statistics
标 题: SQL check likeness between two large tables
发信站: BBS 未名空间站 (Tue May 6 15:38:29 2014, 美东)
I need to check the likeness between two data tables on SQL. I am working on
Aginity Workbench for Netezza on Win 7.
The tables are very large. One of them has 100 million rows and 4 columns;
another one has 1500 million rows and 3 columns.
Example, table1
ID1 ID2 ID3 Value
xxxx xxxxxx xxxxxxxx xxx.xxxxxx ... 阅读全帖 |
|
m*********u
发帖数: 1491 | 13 SELECT *
FROM
(
SELECT *,'80' as top_id
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a
union all
(
SELECT *,'81' as top_id
FROM table1
JOIN
table3
ON table1.id1 = table3.id1
GROUP BY table3.id2, table3.id3
) AS tt_b
GROUP BY top_id, id2, id3 |
|
l******9
发帖数: 579 | 14 【 以下文字转载自 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... 阅读全帖 |
|
g*s
发帖数: 2277 | 15 select temp2.locationid, IFNULL(table1.name,''),temp2.description from
(select locationid, name, description from (select distinct locationid from
table1) temp1 join table2 order by locationid,name) temp2
left join table1 on (temp2.locationid=table1.locationid and temp2.name=
table1.name); |
|
n****u
发帖数: 229 | 16 Sorry I can not write Chinese here.
In MySQL, I have two databases: newdb and olddb
table1 in newdb, table2 and table3 in olddb
table1 has userid, nickname, msn
table2 has userid, nickname
table3 has userid msn
First I use NaviCat to import table2 into table1, very fast! 320,000 records
in 2 mins!
Then I tried to import table 3 into table1, now I have to check userid first
, then UPDATE table1. Very slow. 50,000 records in 12 hours
Anyway I can make it fast?
Thanks |
|
a*******t
发帖数: 891 | 17 double check your query
select distinct table1.A, table1.B --you really mean to pull distinct only?
from table1, table2
where table1.A=RXs.table2.A --what is RXs?
AND table2.B != table2.B --you mean table1.B <> table2.B
order by A --you didn't get an error with this? need to provide table name |
|
e***e
发帖数: 1040 | 18 SELECT CustomerID, Inquery_code FROM table1
WHERE Inquerydate IN (SELECT max(inquerydate) FROM table1,table2 WHERE
table1.CustomerID = table2.CustomerID AND table1.inquerydate
orderdate GROUP BY table1.CustomerID)
包子 please,当然date那个可以自己改一下,懒得写了 |
|
B*****g
发帖数: 34098 | 19 select t1.id, t1.f1,t2.f2,t3.f3
from (select id, last(field1) as f1
from table1
where field1 is not null
group by id) t1,
(select id, last(field2) as f2
from table1
where field2 is not null
group by id) t2,
(select id, last(field3) as f3
from table1
where field3 is not null
group by id) t3
where t1.id = t2.id
and t1.id= t3.id
or
select t0.id,
(select last(t1.field1) from table1 t1 where t1.id = t0.id and t1.fie
ld1 is not null) AS field1,
(select last(t2.field2) from table1 t2 wher |
|
l******9
发帖数: 579 | 20 I need to add a column with an int value to the result from a subquery on
SQL.
It is Netezza SQL.
SELECT *
FROM
(
SELECT *
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a # here, I need to add a new column to tt, call it as top_id
and also assign an int value to it, such as 80
FROM
(
SELECT *
FROM table1
JOIN
table3
ON table1.id1 = table3.... 阅读全帖 |
|
l******9
发帖数: 579 | 21 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... 阅读全帖 |
|
s**********o
发帖数: 14359 | 22 你的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本身... 阅读全帖 |
|
l******9
发帖数: 579 | 23 【 以下文字转载自 Statistics 讨论区 】
发信人: light009 (light009), 信区: Statistics
标 题: SQL check likeness between two large tables
发信站: BBS 未名空间站 (Tue May 6 15:38:29 2014, 美东)
I need to check the likeness between two data tables on SQL. I am working on
Aginity Workbench for Netezza on Win 7.
The tables are very large. One of them has 100 million rows and 4 columns;
another one has 1500 million rows and 3 columns.
Example, table1
ID1 ID2 ID3 Value
xxxx xxxxxx xxxxxxxx xxx.xxxxxx ... 阅读全帖 |
|
l******9
发帖数: 579 | 24 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL combine two tables into one table and add a new column
发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
I need to add a column with an int value to the result from a subquery on
SQL.
It is Netezza SQL.
SELECT *
FROM
(
SELECT *
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a # here, I need to add a new column to tt, c... 阅读全帖 |
|
l******9
发帖数: 579 | 25 【 以下文字转载自 Statistics 讨论区 】
发信人: light009 (light009), 信区: Statistics
标 题: SQL check likeness between two large tables
发信站: BBS 未名空间站 (Tue May 6 15:38:29 2014, 美东)
I need to check the likeness between two data tables on SQL. I am working on
Aginity Workbench for Netezza on Win 7.
The tables are very large. One of them has 100 million rows and 4 columns;
another one has 1500 million rows and 3 columns.
Example, table1
ID1 ID2 ID3 Value
xxxx xxxxxx xxxxxxxx xxx.xxxxxx ... 阅读全帖 |
|
l******9
发帖数: 579 | 26 【 以下文字转载自 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... 阅读全帖 |
|
b*****r
发帖数: 359 | 27 like this?
data new_table; set table1 table2;
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z then
num_new = num + A;
else
num_new = num + B;
run;
这个出错：
ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 557-185: Variable table1 is not an object. |
|
s*z
发帖数: 132 | 28 You can not use set to merge columnwise in data step. Set is used to do
rowwise union.
search for 'sas data step merge', and try something like this:
proc sort data = table1;
by x y z;
proc sort data = table2;
by x y z;
data new_table;
merge table1 table2;
by x y z;
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z
then num_new = num + A;
else num_new = num + B;
run; |
|
l******9
发帖数: 579 | 29 I need to check the likeness between two data tables on SQL. I am working on
Aginity Workbench for Netezza on Win 7.
The tables are very large. One of them has 100 million rows and 4 columns;
another one has 1500 million rows and 3 columns.
Example, table1
ID1 ID2 ID3 Value
xxxx xxxxxx xxxxxxxx xxx.xxxxxx // here x is 0-9 int
table2:
ID1 ID2 Value
xxxx xxxxxx xxx.xxxxxx
the ID1 and ID2 may be duplicated but Values are not duplicated in the sam... 阅读全帖 |
|
l******9
发帖数: 579 | 30 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL combine two tables into one table and add a new column
发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
I need to add a column with an int value to the result from a subquery on
SQL.
It is Netezza SQL.
SELECT *
FROM
(
SELECT *
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a # here, I need to add a new column to tt, c... 阅读全帖 |
|
l******9
发帖数: 579 | 31 【 以下文字转载自 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... 阅读全帖 |
|
s**********i
发帖数: 711 | 32 table1 has bunch of columns, with 'id' as primary key, and
I need to update a column a with max value of table2.b, for
corresponding id.
I tried
replace into table1 (id, a) select id, max(b) from table2 group by id;
this can put the max(b) into table1 a column, but in same time overwrites
all other columns in table1 to default value... which certainly is not
what I want.
anyone know an answer? TIA |
|
x****e
发帖数: 55 | 33 我在自己的PC上用MYSQL做了一下测试，
对于都是5万条记录的两张表 table1, table2
第一个方法：
select * from table1, table2 where table1.ID = table2.ID;
大约需要600秒
第二个方法：
create index table1_index on table1 (id);
create index table2_index on table2 (id);
SELECT * FROM (SELECT ID FROM TB1) A INNER JOIN (SELECT ID FROM TB2) B ON (A
.ID = B.ID)
大约需要350秒
的确快了
第三个方法：
用JAVA编程，随机生成两个size都是5万的array
先分别对两个array排序（从小到大），然后从小比到大，时间复杂度是2×50000
最终运算只需要不到1秒 ！！！
为什么数据库的效率会比JAVA的程序慢那么多？
哪位兄台能否解释一下？
多谢 |
|
I******e
发帖数: 101 | 34 You basically need sort merge join or hash join to do this fast. But, MySQL
does not support any of these. Do you have other databases?
After you create index, can you do:
explain select * from table1, table2 where table1.ID = table2.ID;
and tell us the resule.
BTW, if you output a lot of records, to measure performance, you should do:
select count(*) from table1, table2 where table1.ID = table2.ID;
(A |
|
c***c
发帖数: 6234 | 35 我有一table1，不知为什前面的人把一cloumn设成blob。我有新table叫problem_
history_summary，想得到table1的blob。
我运行
update problem_history_summary set (problem_history, problem_blob，
datestamp，type)= (select UTL_RAW.CAST_TO_VARCHAR2( t.description ), t.
description ，t.datestamp，t.type from table1 t where t.thenumber = '
001A1965');
没有问题。但我设了个trigger，每当table1里insert了new row，我就运行以上的sql
，当然稍微变下
update problem_history_summary set (problem_history, problem_blob，
datestamp，type)= (UTL_RAW.CAST_TO_VARCHAR2( :new.description ), :ne |
|
a*******t
发帖数: 891 | 36 select table1.*
from table1, table2
where table1.A = table2.B
and table1.B <> table2.B
you can try it, not sure if it'll work
and it's recommanded to use "join" operator instead of implied join by using "where" causes |
|
c*******e
发帖数: 8624 | 37 你这个query有问题,
table1:
A B
835448 0
table2:
A B
835448 0
835448 1
这里table1和table2第二个record满足你的条件
但是实际上table2里面有table1里面的record
记住在A相等的情况下,table2里面只要有一条记录不等,
table1里面那条就会被pull进来
一般你可以用minus或者outer join + where ... is null
出， |
|
f*******h
发帖数: 53 | 38 select
m.loationID,table1.name,m.description
from
(select
a.locationID, table2.*
from
(select distinct locationID from table1) a cross join table2) m left outer
join table1 on m.name=table1.name |
|
Y***e
发帖数: 1030 | 39 在不同的资料里看到Alias和join 不同的写法，请教各位前辈是怎么回事。是在不同的
环境下的区别嘛？
1 Alias: 一种带as, 一种不带
SELECT column_name AS alias_column_name
FROM table_name As alias table_name;
和
SELECT column_name alias_column_name
FROM table_name alias table_name;
为什么我在各种教材里看到这两种写法？
2 join: 一种写join... on.., 一种就用where
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
和
SELECT table1_column_name_1, table2_column_name_2
FROM table1， table2
where table1.column_name=table2.column_name;
而且下面只用where 这种写... 阅读全帖 |
|
S****e
发帖数: 10596 | 40 cmd = "SELECT table2.item FROM table2 INNER JOIN table1 ON table1.ID=table2.
ID WHERE !@#!@#!@#!@$^%$%^$% "
有以上一个sql查询语句，目的是通过table1中的条件，选取合适的ID
再通过join ID，在table2中对号入座找到item
现在的问题是，为了方便查询，ID 在table1里面并不唯一，有可能查询到两行具有相同
的ID，然后显示出来的item就重复了
有什么简单方法能够只显示一个ID如果重复
不知道是否说明白
先谢了！ |
|
w*s
发帖数: 7227 | 41 right now my query in c# looks like this,
Table1Entry bMfi = MyDB.Table1.Single(x => x.Name == "abc");
Table1 is like this {Name, field1, field2}.
now i break Table1 into 2 tables,
Table0 has {id, Name};
Table1 has {id, field1, field2}.
What's the easiest way to do a query based on Name now ? |
|
b*****r
发帖数: 359 | 42 有两个表格，如图 table1 和 table2。
要执行的操作：
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z then
num_new = num + A (A is a constant)
else
num_new = num + B (B is another constant)
也就是说，表格1里面有5万条数据，表格2里面有100条数据。
只有当表格1里面的 x,y 和 z 同时等于 表格2里面的x y z
的时候， num_new = num + A; 否则， num_new = num+B.
最后得到的新表格的行数和表格1 是一样多的，也是5万条。
怎样实现呀？
使用 data step 语句 还是 proc SQL ?
谢谢 |
|
s*****n
发帖数: 2174 | 43 CREATE OR REPLACE TEMPORARY TABLE table3 AS
SELECT
type,
name,
ROW_NUMBER () (PARTITION BY type ORDER BY name ASC) AS rn
FROM
table2;
SELECT
table1.type,
MAX(CASE WHEN table3.rn = 1 THEN table3.name ELSE NULL END) AS name1,
MAX(CASE WHEN table3.rn = 2 THEN table3.name ELSE NULL END) AS name2
FROM
table1 LEFT JOIN table3
ON table1.type = table3.type
GROUP BY
table1.type; |
|
l****u
发帖数: 529 | 44 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; |
|
a*******s
发帖数: 324 | 45 select q.sid from (select pid from table1 where sid = X) as p inner join
table1 as q using(pid) where q.sid != X group by 1 having count(*) >=(select
count(*) from table1 where sid = X) limit 1;
MySQL
supplier |
|
l******9
发帖数: 579 | 46 【 以下文字转载自 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 | 47 I am designing a SQL Server 2008 R2 query.
If I used string concatenation to insert into table, it does not work.
DECLARE @s1 varchar(MAX);
DECLARE @s2 varchar(MAX);
DECLARE @s3 varchar(MAX);
DECLARE @s4 varchar(MAX);
SET @s1 = 'SELECT a.id, b.name as new_name, a.value FROM ['
SET @s2 = '].[dbo].[table1] as a, '
SET @s3 = 'a_temp_table as b ' -- a_temp_table is a table variable. No
matter I put "@" or "#" in front of a_temp_table, it doe snot work.
SET @s4 = 'WHERE a.id = b.i... 阅读全帖 |
|
l******9
发帖数: 579 | 48 I need to so a sql query on IBM netezza sql database from Aginity workbench
on win7.
My query:
SELECT *
from table1 AS c ,
table2 AS b
where CAST(c.id as int) = b.id
in table1, id is character varying(20) and in table2, id is int.
Table1:
id value1 value2
'985' 'casdqwdc' '654.3184' // they are all char
Table2:
id value1
985 694381 // id is int, value1 is int
I got error:
ERROR [HY000] ERROR: pg_atoi: error in "id": can't parse "id"
Any help would be apprec... 阅读全帖 |
|
G***G
发帖数: 16778 | 49 want to create a table like this
select table1.*, count(distinct table1.field1) from table1
but it always pops up an error. |
|
b******d
发帖数: 1948 | 50 no man, you need a 'group by' here, google 'group by' see what ppl say.
If you want select * from table1 + count(column1), you need do a join
also.
select t.*, x.cnt from table1 t inner join (
select column1, count(pk) as cnt from table1
) as x on t.column1 = x.column1
pk is the primarykey, |
|
