I**A
发帖数: 2345 | 1 那,那个query就不对啊。。
我的query就是
select * from
student
left join enrollment on sid...
left join courses on cid... AND cname='math' |
|
b*******d
发帖数: 750 | 2 哥也被问到这道题了，略有不同，给出了incoming query的distribution的曲线，qps
是几千这样子。这个system有三个api：
1）getTopTenMillionInPastHour()
2) isInTopTenMilionInPastHour()
3) notifyWhenJoinOrRemovedFromTopTenMillionInPastHour()
自己来architect系统，定义service能提供数据的精度。
我当时给的是个多台机器的架构。我觉得是旧data每秒钟都在产生（任何当前时间进行
的api call，准确来说，1小时零1秒前的数据都是旧的，对算top10millioninPastHour
没有意义），但几乎没有任何系统在一秒钟内可以purge掉这么多的旧数据，所以就要
定义一个精度。比如，semantics是“我能保证给结果是精确的，但是有个最多20分钟
的delay”，或者“我不能保证结果是100%精确的，但是我能最新的query能够实时的反
应在结果中，并且返回的结果和真值有90%的重合”。
其实就是说consistency，a... 阅读全帖 |
|
t*****e
发帖数: 53 | 3 踢掉最少最老的记录
But those queries might showed again in the future? they might be in the
list of top 10 queries. |
|
q****x
发帖数: 7404 | 4 命令行下，每次手动执行query后能自动显示这次query用多少时间。 |
|
k*******r
发帖数: 355 | 5 我知道无限stream中维持topK frequent query是用hashtable加一个大小为K的minheap
但sliding window中维持topk频繁的query这应该怎么做？如果用一个大小为K的
minheap再加一个大小为N-k的maxheap, 再加上hash table应该可以搞定。但通常N>>K
所以维持N-k的maxheap代价太大。
这题标准答案应该是什么样的？ |
|
k*******r
发帖数: 355 | 6 就是比如在任意时刻，会查当过去8小时的top100个访问最频繁的google query. 现在
要求设计一种数据结构方便这种查询
相当于有一个8小时的time window不断滑动，随时要查这个time window内访问最频繁
的top100 query |
|
l******9
发帖数: 579 | 7 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 | 8 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'
Any help would be appreciated. |
|
l******9
发帖数: 579 | 9 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... 阅读全帖 |
|
z***u
发帖数: 105 | 10 用KDTree 在二维平面里来找某个query点附近最近的一点。请问如何处理这种特殊情况？
离query点x的最近的点*3在没有搜到的树杈上。因为从KDTree的root *1下来，会到左
半平面，它不会走到右半部分。
*1
*2 |
------------|
x | *3 |
|
D******e
发帖数: 72 | 11 目前有一些Speech Services 和 Substrate & Query Intelligence 相关的职位在招人
，有SDE,
PM, Data Scientist。
具体职位信息可以参考如下链接：
Speech Services: https://careers.microsoft.com/search.aspx?
pg=0#&&p2=all&p1=all&p3=all&p4=all&p0=xxssrecruitXX&p5=all
Substrate & Query Intelligence: https://careers.microsoft.com/search.aspx?
pg=0#&&p2=all&p1=all&p3=all&p4=all&p0=XXOIRecruitXX&p5=all
如果有兴趣的话，欢迎发 email 到 [email protected]/* */。
劳烦发信的时候说明：
您感兴趣的职位编号（或者给出链接）
您的一个简要自我介绍（英文，第三人称，200字左右就好）
您的简历
您想问的问题。
我会每天早晚收邮件（9 am 之前或者 ... 阅读全帖 |
|
w*r
发帖数: 2421 | 12 【 以下文字转载自 Database 讨论区 】
发信人: MMinhouston2 (MMinhouston2), 信区: Database
标 题: 包子请教query
发信站: BBS 未名空间站 (Wed Sep 3 11:38:21 2008)
请教一下这个query应该怎样写哈
有call history table如下,一个ID可能被call多次：
ID callresult calldate
1 null null
1 R sep 2
2 S sep 1
3 null null
3 null null
……
我想找出callresult从来都是null的ID，曾经有过null但是还是有别的result的不包括
在内。
这个应该怎样写？
谢谢啦！ |
|
M*****a
发帖数: 2054 | 13 query the index file
index the documents
retrive through query |
|
n*w
发帖数: 41 | 14 In MS SQL 7, how to query a tree,
i.e. if I have a table as follows:
ID--CONTENT--PARENTID
1--'XX'--0
2--'XX'--0
3--'XX'--0
4--'YY'--0
5--'XX'--2
6--'XX'--2
7--'WW'--5
...
How to use SQL to query either the whole structure of the tree or one whole branch?
Thx |
|
h*****l
发帖数: 184 | 15 【 以下文字转载自 BuildingWeb 讨论区,原文如下 】
发信人: hanibal (汉尼拔), 信区: BuildingWeb
标 题: ASP问题： Operation must use an updateable query
发信站: The unknown SPACE (Fri Jun 8 23:04:03 2001) WWW-POST
我用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.ope |
|
h***i
发帖数: 11 | 16 hi, DB experts. here i have an problem to create this query:
i have a table:
T1 {fieldID, fieldvisitTime, fieldUserID, fieldIP}
now i want:
get the last visit time and its corresponding IP for each userID.
I think it's possible to make this query in one SQL sentence. but how?
thanks in advance!!!!!!!!!!!! |
|
j**o
发帖数: 47 | 17 hi friend:
I want to do this parameter query in MS Access,
to search a person, if I know the person's name, then I type the name,but
if I dont know the person's name, then display all the persons in the
database.
How to writte the query criteria code?
Thansk |
|
j**i
发帖数: 419 | 18 I have some huge tables to generate dynamic DDL,each table has about 30-50
fields,some table have 1 primary key, some table have 4 primary key like this:
Table T1 (PK1),T2(PK1,PK2,PK3,PK4),T3(PK1,PK2,PK3,PK4),T4(PK1,PK2,PK3,PK4),.
,
I may need to up to 200 fields in this query( may from 4-5 tables or more) and
I need to return 4000-5000 records each time at an acceptable speed.
The idea is:
use PK1 in TB1, then join other tables-T2,T3,T4 ...etc.
record number:
how many there in the query('X1','X |
|
m****c
发帖数: 8 | 19
It depends on what kind of view you are going to create. In oracle, they have
materialized view, that will speed up the query. But you still need to check
the query executing path, and make sure using the correct index, joins etc. |
|
aw
发帖数: 127 | 20 for sql server, you can try to look at "show execution plan" under sql query
analyzer -> query.
a few quick fixes: add indexes, avoid "not in", use temp tables over
cursors... |
|
c*****r
发帖数: 4 | 21 多谢详细回复. 刚从面试回来. 那些人还算手下留情, 没有专找难的问, 问了些基本问题
, 如normalization, 黑板上给几个table, 要求写点复杂的SQL Query, 还有些indexing
的概念问题. 然后很详细的问了简历中所有的关于数据库的部分. 感觉这些人很懂, 一下
就抓住了project的要害(后来才知道, 他们中有四个是Ph.D.), 然后问了join的优化, 能
说出几种, 每种怎么实现, hash join在具体例子中的实现等等. 还问了一些算法,
如quick sort分析(不知为什么问这个). 他们的query optimization 的程序就有上百万
行, 让 我咋舌. 就这么多了, 面试得很累, 结果也不知道, 说到时综合讨论了通知. 再
次多谢指点. |
|
l******e
发帖数: 956 | 22 I have a table like
id parent_id lastname firstname
1 0 A 1
2 0 B 1
3 1 A 2
4 3 A 3
....
So you can see id 1 is parent of id 3, id 3 is parent of id 4
How to right a single SQL query, when you give id = 1, it will return its own
record along with id 3 and id 4 records as its child and grandchild ?
Also, how to write a SQL query, when you gice id = 4, it will return its own
re |
|
n****f
发帖数: 905 | 23 Q: How to right a single SQL query, when you give id = 1, it will return its
own record along with id 3 and id 4 records as its child and grandchild ?
SELECT *
FROM Your_Table_Name
CONNECT BY PRIOR ID = parent_id
START WITH ID = 1
Q. Also, how to write a SQL query, when you gice id = 4, it will return its
own
record along with id 3 and id 1 records as its parent and grandparent ?
SELECT *
FROM Your_Table_Name
CONNECT BY PRIOR parent_id = id
START WITH ID = 4
这是典型的 recursive q |
|
k*****t
发帖数: 161 | 24 问一个Squery
表 book
Field 1: bookID
Field 2: bookName
bookID 是primary key
bookName is unique
表 reader
Field 1: bookID
Field 2: readerName
表reader的主键是 (bookID, readerName)
现在需要找出所有的两本书，它们有相同的读者群。
例如
表book中的记录是：
1，a
2, b
3, c
表reader中的记录是：
1, w
1, x
2, w
2, x
3, y
Query出来的结果是
a
b
这个 Query怎么写？
Thanks. |
|
y*l
发帖数: 334 | 25 哪位高手回答一下。
我用了两个query:
select readerBookID,count(readerBookID)From Reader
group by readerBookID
having count(readerBookID)>=2;
得出答案是1,2
然后再回表Book找到a,b
但这种方法在很多records的情况下肯定是不行的。谁教一下怎么合并两个query？谢了先
。 |
|
c***y
发帖数: 114 | 26 thanks
it supports nested queries from 4.0, i'm using 3.21 :(
i figured it out by store the output from sub-query into an array |
|
a*******t
发帖数: 891 | 27 what database are you using?
the query itself can't run in MS Access.
have you tried it in the database itself?
Table2 have only one record. table1 have more than 10,000 records. Want to get
max(date) and update table2.
run it and got error message:
"Operation Must Be Updatable Query". I know it is the link problem. No idea ho
w to deal with it or is there any other way to work on it? Thanks. Help please
. |
|
f**c
发帖数: 629 | 28 I am new to database. Could any one help on this easy query? Thanks alot.
Given the following SQL table:
create table myTable (
id int not null,
val varchar(2) not null
);
With values:
id val
-------
1 b
2 c
3 a
4 b
5 c
Write a single SQL query in MySQL 3.23 which will output the
following result, which is a list of records with equal 'val'
fields:
+-----+-----+-----+
|
|
c*****t
发帖数: 1879 | 29 No, you misunderstood. Compiled plan doesn't mean its execution plan
would be exactly the same as the direct SQL query execution plan. The
difference thus can lead to performance difference.
I'd suggest you to write a stored procedure that does direct query
and compare. |
|
c*****t
发帖数: 1879 | 30 The way database resolves query starts from actual data,
and then apply filters, does joins etc. Not the other way
around. This is how fundamentally databases work.
In this case, 1st query is resolved by applying predicates
on table B and table C (i.e. B.ITEM_B = 123). From here,
generates the necessary B.ID, and C.ID which can then be
compared with A.ID. If B.ITEM_B = 123 generates NULL set,
there won't even be a join operation.
Also, prior join / set operations, usually there are internal |
|
s*******u
发帖数: 19 | 31 It seems it does not make sense to create another table for this query if
the data is dynamic in Table A. Moreoevr, it loses the business logic
meaning by querying table B in your way even you can do this. |
|
g*********e
发帖数: 29 | 32 Thanks for the reply. Seattlewu's method is great but the real data is huge,
hence, we cannot insert data manually; Kissbigeye's idea is practical,
however, I am just going to query from one table.
This table includes thousands of accounts and other variables.
The table looks like:
AccountNum amount paymentdate .......
a xx 04/01/08
a xx 04/05/08
c xx 04/05/08
a xx 04/09/08
.
.
.
I want to get the query 1 |
|
t*g
发帖数: 1758 | 33 正在重写一个没有source code的程序。可是不知道某些data怎么来的。所以需要找到
原始query。能capture 这个query吗？log里能看到吗？怎么看呢？谢谢！ |
|
c***n
发帖数: 921 | 34 请教2个sql query 问题:
1. 假设有table A, and A has attribute k1, k2, k3, k4, k5
怎样判断{k1, k2, k3} 是否是一个candidate key? 也就是说是否{k1, k2, k3}
uniquely identify each row.
2. 假设有table A, and A has a numeric attribute "date". It stores
information like "200604", which means April in year 2006. Now I want to
derive a new table B, which contains two numerical attribute "Month" and "
year" from A. How to write the query?
也就是说从原来的 200604 变成 2006 和 4, 都是numeric attribute.
谢谢看完我的问题. |
|
e*u
发帖数: 17 | 35 Have you ever checked your query plan? or even your query?
At least yours is not correct in SQL Server. |
|
c*****t
发帖数: 1879 | 36 我有一 DAG table T，里面是
src name,
dst name,
value int
比如
a, b, 1
b, c, 2
a, d, 3
我现在需要做很多 path 的 query，需要得到 int[]，也就是说
select getPath ('a', 'c');
结果应该是
[1,2]
有可能有多种 path，可以取最短的。
我之所以需要做该 query，是我有另外一个 table inputTable 是 pair，
需要得到。注意的是该 table 里有很多重复的 src, dst 。
select compute (getPath (T.src, T.dst), T.cost)
from inputTable T;
有什么好的办法？
多谢 |
|
n********6
发帖数: 1511 | 37 我以前遇到过类似的问题，不敢用select top。高人指点一下select top如何产生？
msdn说top在order by时按顺序产生.高人解释一下。
If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.
MSDN说top在insert, update时是随机的。
The rows referenced in the TOP expression used with INSERT, UPDATE, MERGE,
or DELETE are not arranged in any order. TOP n returns n random rows.
http://msdn.microsoft.com/e |
|
j*****n
发帖数: 1781 | 38 近日写了一个比较复杂的query. 用了SQL Server 2005 提供的CTE.
由于这个query经常要用到，而且想着SP is pre-complied, 所以就建了一个SP。
谁想在SSMS里面几分钟就能出的结果,SP愣花了半小时也出不了结果...
有哪位碰到过类似的问题么？ |
|
c*****t
发帖数: 1879 | 39 I am trying to implement foreign key query myself. That is, given
table S, and a set of columns (s1, .. sn) in S, check if all set of
values are in the table T and unique key columns (t1, ... t).
What's the most efficient query?
thx |
|
s**m
发帖数: 1564 | 40 there is no direct way or features to do this. google is your friend. see
the quote below:
SQL Server doesn't really track this information. You would have to use
Profiler or a custom tracing mechanism to audit all of the queries.
There are some built-in reports in Management Studio that will show you top
10 queries by cpu, i/o etc., and you can also write your own against the new
DMVs (which provide much more real-time information than sp_who/sp_who2 etc
). See the following for some ideas: |
|
gy
发帖数: 620 | 41 今天在跟朋友聊这个query时, 发现凡是compatibility_level是80的都不能run这个
query, 不管是在2005还是在2008里. 那是不是可以说, compatibility是80的有很多
new feature是没法体现的呢?? (具体有哪些, 目前不知道..)
(刚开始我还以为是build的问题, 后来聊天时觉得M$不应该有这么大的漏洞, 于是怀疑
其他的, 果然被俺发现了. hehe) |
|
c*****t
发帖数: 1879 | 42 【 以下文字转载自 CS 讨论区 】
发信人: coconut (向唐僧大师学习中), 信区: CS
标 题: 谁能推荐关于 sql query optimization 方面的书？
发信站: BBS 未名空间站 (Sat Aug 29 18:57:28 2009, 美东)
谁能推荐本 sql query optimization 方面的书？能有 parallel 部分的更好。
thx |
|
c***c
发帖数: 6234 | 43 我觉得纯query是没法做到的。因为纯query没法用if判断。
而且没法知道是第一和第二。
用stored procedure试试把，也得建立temp table或者返回3个arrays，就是CusipCol
，IDCol， ValueCol各在一个array里面 |
|
d*******n
发帖数: 524 | 44 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 |
|
l********u
发帖数: 195 | 45 table 1
product no. description
001 aa
002 bb
003 cc
table 2
Product no transcaction
001 111
001 222
003 rrr
how can I write query to find the product in table 1 that doesn't have any
transaction in table 2. query should return 2 by using the above data. |
|
l********u
发帖数: 195 | 46 we need to put this query into a tool. however the tool has
"select * from table t1 where" hardcoded, left join is before where, so how
can I left join with query start with select * from where |
|
l********u
发帖数: 195 | 47 we need to put this query into a tool. however the tool has
"select * from table t1 where" hardcoded, left join is before where, so how
can I left join with query start with select * from where |
|
l********u
发帖数: 195 | 48 select * from db.f4311 A where
NOT EXISTS (SELECT 1 FROM TESTDTA.F43121 WHERE A.PDDOCO=PRDOCO AND A.PDDCTO=
PRDCTO AND A.PDKCOO=PRKCOO )
and (NOT EXISTS (Select 1 from TESTDTA.f4311 B where A.pddoco = b.pddoco
AND A.pddcto = B.pddcto AND (B.pdtrdj not between @SD and @ED or B.pdnxtr
not in ('999','980') or B.pdlttr not in ('999','980'))))
Tried to run this query and it's been running very long without return. how
can I speed up the query? |
|
h***r
发帖数: 184 | 49 一个select query，criteria是field1等于１，field2是group by field3里最大的。
我写了下面的query，可是总告诉我最多只能return一个record，而在我的data set里
符合标准的应该有几百个才对。我用Access2007。请高人指点。
SELECT Table.*
FROM Table
WHERE Field1 = 1
AND Table.Field2 = (SELECT max(Field2) FROM Table GROUP BY Field3) |
|
g***l
发帖数: 18555 | 50 我已经给你改好了，ROW_NUMBER等于是重新对你的QUERY排序，你不能在排序的时候
FILTER，而必须到排完序之后的QUERY再FILTER。 |
|
