g***l
发帖数: 18555 | 1 对，SUBQUERY很容易返回多个数值，很悬，就多写几个QUERY，放在VARIABLE里。通常
出错都是这种COMBINE的QUERY，本来想返回一个值的，出来多个或者一个也没有。有时候
想飞，结果不是摔在地上就是翅膀扑通折了。 |
|
o***i
发帖数: 603 | 2 The output order depends on key, query plan and index and so on ...
If you care the order, you MUST use an order by. There is no kind of "defaul
t order". |
|
s******0
发帖数: 13782 | 3 找到两个：
Toad Data point, Toad for Data Analyst - Cross-connection queries
AQT - Advanced Query Tool |
|
|
l******y
发帖数: 60 | 5 有两个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中的以下列都要选出：
... 阅读全帖 |
|
g*********n
发帖数: 43 | 6 Two tables, the first one "Users" saves the user name and some properties of
the user, age, gender, . . and the last column is "hobbies", to save the
hobbies for this particular user. Since hobbies can be quite diverse, in
this table we put an id of the hobbies.
name age gender hobby_id
A 20 M 1
B 45 F 1
C 52 M 2
. . .
And we define another table to map this id into real hobbies:
ho... 阅读全帖 |
|
r**********d
发帖数: 510 | 7 Space between code and from? General tip for debugging query is print out
the query that you asks the to run and run it in DBS. That fixes a lot of
errors for me |
|
l******9
发帖数: 579 | 8 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... 阅读全帖 |
|
h****t
发帖数: 22 | 9 由于CTE query 的结果需要重复使用，目前将结果保存在表里，通过trigger来更新表
。由于相关表会经常更新，导致结果表跟随着更新，多多少少带来性能上的影响。不知
能够实现在query结果表时才进行重新计算（如果相关表修改过了）。请各位高人提供
思路，谢谢！ |
|
s**********o
发帖数: 14359 | 10 CTE recursive query, trigger这些对PERFORMANCE影响很坏的，
有时候会产生你意想不到的结果，为什么老要更新呢？能不能
搞成SCHEDULED JOBS呢，如果必须不断更新，考虑所有应用程序和
QUERY的SELECTION都用WITH (NOLOCK) |
|
m******u
发帖数: 12400 | 11 I can run the query. But I just don't like the query. |
|
m******u
发帖数: 12400 | 12 thanks. I like your query.
发信人: ntycl (你太有才了), 信区: Database
标 题: Re: 这个query对么？
发信站: BBS 未名空间站 (Mon Jul 6 23:43:31 2015, 美东)
using lag() over() to access data from previous row:
WITH LagDate as (
SELECT ID, [date], temp,
LAG (temp, 1, 0) over (order by [Date] ASC) as LagTemp
FROM Weather
)
SELECT id, [date] AS [Dates with temp higher than previous day], temp
FROM LagDate
WHERE temp > LagTemp |
|
k****n
发帖数: 52 | 13 类似SQL或者任何方式的query? 有可能对在memory的数据作SQL query吗？多谢！ |
|
w*s
发帖数: 7227 | 14 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 ? |
|
f*******4
发帖数: 345 | 15 It seems you don't have to use projection at all, but you have to use left
outer join for sure.
The query could be like this:
select count(distinct s.id) from State s left join s.customer as c left join
c.purchases as p where c.gender="male" and p.item="pc" group by c.id having
count(p)>1000
If the query is really slow, you've to figure out some quirky way such as
adding a column into the state table as customer counter, and in your
business logic whenever a purchase happens, update the column |
|
l**********r
发帖数: 4612 | 16 【 以下文字转载自 Linux 讨论区 】
发信人: linuxbeginer (linux), 信区: Linux
标 题: emacs里能不能从后往前的query-replace？
发信站: BBS 未名空间站 (Wed Jan 2 19:56:37 2008)
有没有办法backward的query-replace？ |
|
W***o
发帖数: 6519 | 17 我觉得你这个不难啊
两步吧：
1. string/title similarity measurement
2. if found the similar title, do a POST query to your REST api, get the
returned results
or if you mean this?
POST your title to the url for query, the server will run some similarity
measurement algorithms in your flat file. Then return results
和 |
|
g*********e
发帖数: 14401 | 18 i have an array, each of them is a string of a testname.
then a need to do a sql query "select ... where TESTNAME=testname ... " for
each tests, then collect each query result back and form the eventual
response.
[{test1: test1_query_result},
{test2: test2_query_result},
...
]
that's what i want. can anyone share usable code? Thx. |
|
n*****t
发帖数: 22014 | 19 某记录，第一次 query name = tom，中间被其他程序 update 了，第二次 name =
marry
两次 query 用不同 name 得到同一个 userid |
|
s********k
发帖数: 6180 | 20 能不能直接对JSON中的field做query，比如我的表里面有field是一个JSON的stream
[{time1:value1},{time2:value2}...],希望对特定的time或者特定比如大于某个值做
query |
|
M****h
发帖数: 11 | 21 投稿PRL，三个审稿人，修改后二审意见三个都回来了，想着应该是要便捷给我们发过
来了，可是奇怪的是编辑竟然给两个审稿人发送了query to referee，状态又变成with
referee了，有经验的前辈看看什么情况，以前从来没有碰到过这种状态.
CURRENT STATUS OF MANUSCRIPT: With referee(s)
Copyright/Right to Publish received
CORRESPONDENCE:
SENT RECEIVED DESCRIPTION
04Apr13 Query to referee
08Mar13 04Apr13 Review request to referee; report received |
|
j******n
发帖数: 271 | 22 cat << xxxx | mail -s "help" E*****[email protected]
We have a table (T_1) with e.g. 4 columns
T_1:
x y x z
1 2 3 4
3 6 1 -1
6 3 2 0
5 3 2 -10
I need a query to find out in each row which column has the max absolute
value.
That is, I need to get the following table as output:
max_col
z
y
x
z
How to write this query in Oracle?
Thanks
xxxx
| |
|
l******9
发帖数: 579 | 23 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 | 24 I am working on Access 2012 on win 7. In a table, I need to change a year
date format from '1-Jan-06' to 2006 and from '1-Jan-90' to 1990. I only need
year.
This is my query
SELECT *,
CASE
WHEN CAST(right(year_date,2) , INT) <= 12
THEN 2000 + CAST(right(year_date,2) , INT)
ELSE 1900 + CAST(right(year_date,2) , INT)
END
FROM my_table;
I got error:
syntax error (missing operator) in the query expression of 'CASE -- END'. |
|
s******r
发帖数: 1524 | 25 For instance,
first_name subject
smith science
smith arts
the query will pick up both. your query will only pick up the first one. |
|
k*z
发帖数: 4704 | 26 【 以下文字转载自 Database 讨论区 】
发信人: kiz (泥偶), 信区: Database
标 题: 有大侠知道怎么format下面这个query的时间么
发信站: BBS 未名空间站 (Wed Nov 13 12:27:16 2013, 美东)
DB2出来的时间大多数是Hundred Year Date, 我现在写了一个query可以把时间取出来
，但是格式是datetime, 我怎么才能转成date?
ELECT date(days('1899-12-31')+HYD) as SQLDate,
Year(date(days('1899-12-31')+HYD)) as Year,
Month(date(days('1899-12-31')+HYD)) as Month,
Day(date(days('1899-12-31')+HYD)) as Day,
FROM DB2.Table
谢谢。 |
|
|
t****n
发帖数: 263 | 28 The result is right. There is no problem with the query.
This might be easier to understand:
SELECT COUNT(*)
FROM dbo.courses c
JOIN dbo.enrollment e
ON c.cid = e.cid
AND c.cname = 'Math'
RIGHT JOIN dbo.students s
ON e.SID = s.SID
WHERE c.cid IS NULL |
|
|
g*********s
发帖数: 1782 | 30 hartime123 once posted the doubt and it seems not answered yet.
we all know universal hash is a hash function family H that has the
similar hash property.
for the insert op, we randomly select h from H and apply h to the input
key k and put it to h(k)
but what if now we need to query if k is in the hash table? we don't know
which hash function h is used to insert k. iterating H one by one
looks too naive. |
|
j**w
发帖数: 382 | 31
Insert: When save h(k), save the original k together.
Query: Compute h_i(y) for every hash function h_i in H, then check all
content at h_i(y) |
|
z*********8
发帖数: 2070 | 32 该片段必须：
1。 包含query
2。 关联性高
这个第二点该怎么判断？ |
|
b**********5
发帖数: 7881 | 33 我给你我的idea吧， 我看了看， 也懒的做那题。
比如说，I think i love the deep dish pizza is great. there are a lot of
pizza stores on St.Peters st. where I lived. deep dish is not my thing。
dish pizza is my thing
然后你的query是deep dish pizza
然后你search第一个句子， 就match 1 个length 3
然后第二个句子， 就match一个 length 1
然后第三个句子， 就match一个 length 2
第四句， 就match二个 length 1
length 3 给最重的weight 1/2, length 2 given weight 1/4, length 3 given
weight 1/8
然后compute score |
|
l******9
发帖数: 579 | 34 【 以下文字转载自 Quant 讨论区 】
发信人: light009 (light009), 信区: Quant
标 题: error run SQL query from pyodbc python 3.2.5
发信站: BBS 未名空间站 (Thu Jun 19 12:04:26 2014, 美东)
I am working on pyodbc in Eclipse (4.3.2v20140221-1852) with PyDev on Win 7.
My python is 3.2.5.
At my code:
cursor.execute("select top " + str(1) + " a.my_id, a.mycode" +
"from my_table as a where a.mycode = ?", aGivenCode)
I got an error :
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server
Driver]
[SQL Ser... 阅读全帖 |
|
c*********y
发帖数: 316 | 35 Oracle:
INSERT INTO (SELECT department_id, department_name, location_id
FROM departments WHERE location_id < 2000 WITH CHECK OPTION)
VALUES (9999, 'Entertainment', 1500);
why have select statement for insert columns? and also that where clause
does not make a lot of sense to me...
can someone one tell me what it means and what is the purpose for this kind
of query?
Thanks! |
|
c*********y
发帖数: 316 | 36 yeah.
but what is the practical purpose for this kind of query?
null。 |
|
g*****g
发帖数: 34805 | 37 If you don't know what the query will look like, C* is a bad choice. You may
try something like Elastic Search. |
|
m*****u
发帖数: 19562 | 38 在eloan之类的网站上check mortgage rate影响 credit score吗?
有的都要求ssn的?
如果打算货比三家的话，总要query的啊? |
|
s******t
发帖数: 2374 | 39 今天看到chase southwest居然收了我年费，我就打电话去很猥琐的要关卡，结果那个
agent很快就给关掉了。赫赫。
关掉之后他又和我说每年都给5000pts，我算了算也差不多就是年费了。又觉得留着也
行。。。
于是我又想了想要不然把卡重新开了吧。
我的问题是，重新开卡对信用记录有影响吗？那个agent告诉我是hard query，所以我
让他hold了一下。
我的卡的creditline还挺高的，有1万5.不知道对信用记录影像大不大。如果重新开的
话，又有多大？
我主要是打算今年买房子。。。。怕这个对credit影响太大了。
谢谢各位了。 |
|
x****i
发帖数: 353 | 40 The title was cut:
Attorney asked me to refrain from querying with USCIS. |
|
x**********a
发帖数: 1372 | 41 我律师也说了这个，但是不是说查网站，而是说打电话和info pass。
说如果你经常问，就会增加他们对你background的怀疑。
说uscis数据库里记录你打电话和info pass这种soft query的次数。如果到一定次数就
会加一些background check。
我也不知道是不是该信他。 |
|
x****i
发帖数: 353 | 42 Hi friends,
Any of you ever queried via emails? Did you get response? If you did, it
took how long?
I sent mine 2 weeks ago but no response as of now. Really doubt if anyone
would take care of it. |
|
m******o
发帖数: 61 | 43 【 以下文字转载自 Database 讨论区 】
发信人: myyaoyao (幸福快乐地生活), 信区: Database
标 题: 请问这个query怎么做
发信站: BBS 未名空间站 (Thu Jul 15 13:16:11 2010, 美东)
TABLE 1:
locationID name
1 aaa
1 bbb
2 bbb
TABLE 2:
name description
aaa AAAA
bbb BBBB
ccc CCCC
我想要的结果是：
locationID name description
1 aaa AAAA
1 bbb BBBB
1 CCCC --- 这一行，TABLE 1 没有CCCC 的name,但是也要列出来。
2 AAAA
2 bbb BBBB
2 |
|
w*r
发帖数: 2421 | 44 Houston, 拿包子来，我这个query要换包子的，只scan table一次,没有join,没有复杂
逻辑，纯粹的number crunching, 如果有index更快，所以要三个包子！ |
|
|
e****t
发帖数: 17914 | 46
自己的项目，取些random sample
一直用query browser 不知道版上大师没都用啥 |
|
h*****l
发帖数: 184 | 47 我用ASP处理ACCESS数据库。
但运行INSERT时，出来错：
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Operation must use
an updateable query.
百思不得其解， 请帮我看看， 错误出在SQLInsert那句上：
Set corConn = Server.CreateObject("ADODB.Connection")
dbpath = Server.mappath("fpdb\mbaalumni.mdb")
corConn.open "driver={Microsoft Access Driver (*.mdb)}; dbq=
"&dbpath&""
RS = Server.CreateObject("ADODB.Recordset")
SQLSel = "Select * from mbaalumni Where fname = '"&
Request.Form("fname") |
|
N**e
发帖数: 158 | 48
try to print the query out here, if it's right(no empty fields, no bad values)
then double check the table format.
I suspect it's the .Form call that cause the problem |
|
