g*********e
发帖数: 29 | 1 Yes, probably it doesn't make sense here. But I really want to do is:
there are many accounts in the database, each account has made a different
payments along with the payment date.
I want to see the first pay of each account, the second pay of each account.
.....
It's easy to see the first pay of each account, for example,
select accountNum, sum(amount),min(paymentdate) from tablename group by
accountNum (it's ok?)
But how to query the second, third pay...makes me headache. That's why I
post t |
|
t**n
发帖数: 30 | 2 Can you query while sort it by date? |
|
s*******u
发帖数: 19 | 3 The 'tablename' in Kissbigeye's query is the same table. This is called self
-join -- you probably should try a little more to learn SQL.
huge, |
|
s****l
发帖数: 41 | 4 请教一个求职面试题：如何写一个SQL query求表中对角线的和 |
|
j*****n
发帖数: 1781 | 5 well, it is 43$ value...
http://safari.oreilly.com/1565927567/transqlcook-CHP-3-SECT-16
this is a calculation of a squared matrix trace or sum of its n eigenvalues.
just tell the interviewer go to hell, SQL query is not dedicated to do such
mathematic calculation. there is tons of statistical analysis software can
easily resolve this problem, even you can use C++ do handle it.
at least I am not stupid enough to consider using SQL. |
|
M*****r
发帖数: 1536 | 6 我估计哈，原意是想要一个非常fancy/tricky的SQL Query， 一句搞定 :-) |
|
M*****r
发帖数: 1536 | 7 sql server trace (query analyzer) |
|
f********0
发帖数: 73 | 8 I have 2 tables a ( with columns a1(string) and a2) and b (with columns b1(
string) ).
For each record in a, if a1 is contained with any one or more record of b1,
the a2 is updated to 1. How to write a mysql query for this.(like a1="abc",
one b1="4abct", the corresponding a2 in the record is updated to 1).
Thanks for your help. |
|
m*****h
发帖数: 18 | 9 I have 2 tables, classes and classSchedules
Classes table has the following columns: ClassID, Title, Description, Length
, Price.
ClassSchedule table has ScheduleID, ClassID, StartDate.
I need a query to return the result shown below
Title Description StartDate Length Price
Class 1 desc. For Class 1 9/1/2008 5 days $1,000 |
|
w*r
发帖数: 2421 | 10 这种显示的问题应该在client端解决，你这个query要加上order by 才能显示正确 |
|
m**********2
发帖数: 2252 | 11 请教一下这个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的不包括
在内。
这个应该怎样写？
谢谢啦！ |
|
e*u
发帖数: 17 | 12 if there is null in the ID column, something has already been wrong.
No matter how you write the query. |
|
e*u
发帖数: 17 | 13 Well, I don't think "not exists" can get anything different from
"not in" in case of NULL. Maybe you can write your query out.
Also, I don't this solve the problem.
If the ID column is NULLable, and the user want to get the NULL out
for whatever purpose, there has to be something wrong. What a NULL
stands for? |
|
d*******8
发帖数: 3182 | 14 这不是sql query,楼主并没有阐明是oracle环境。
不可否认，在oracle环境中，这份方案是很不错的。 |
|
j*****n
发帖数: 1781 | 15 if MS SQL Server 2005, go books online and look for "Recursive Queries Using
Common Table Expressions", there is an example exactly what you want. |
|
cd
发帖数: 32 | 16 谢谢beijing， 是oracle。
但是你的query在col_a里面没有4出现的时候没有返回值。 |
|
h****n
发帖数: 101 | 17
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.
你觉得insert/update的时候顺序重要吗？order by的时候呢？ |
|
M***7
发帖数: 2420 | 18 Sorry for the confusion.
this query is like suppose I have a sequence a1 with "agdgteddgg", then I
put it into the table as below:
col1 col2 col3
a1 1 a
a1 2 g
a1 3 d
...
a1 10 g
then I get 9 other different sequences (a2-a10) and put them into the table
in the same format.
Then I want to check for all sequences, what is the combination of
composition at position 2 and position 8.
so it could be |
|
r****r
发帖数: 1693 | 19 想了好久,没有解决,请帮忙看一下,多谢!
name product total_num
A paper 3
A pen 2
A paper 3
A water 2
A water 2
A paper 3
A pen 2
变量total_num表明相关的product总共出现多少次,
现在需要选出出现最多的那个产品和次数,如下
A paper 3,
请问该怎么写query呢?多谢. |
|
r****r
发帖数: 1693 | 20 select distinct A,B,max(C),D... from table group by A,B,D... order by
A,B,D
这个好像不行,结果是
A paper 3
A water 3
A pen 3,
但是无法选出拥有最大的total_num (3)的相应的product，
想要得结果是
A paper 3,
如何能在一步query里面,智能的找出某个变量的最大值,
并且同时选出其相应的另外一个变量值呢？ |
|
s****u
发帖数: 23 | 21 query里有个field是SSN,like 123－45-6789.我需要的format should be like:１２３
４５６７８９． 应该怎样去掉ｄａｓｈ呢？谢谢先？ |
|
k***e
发帖数: 7933 | 22 oracle里面怎么查过去一小时里run过的所有query？
from |
|
z***y
发帖数: 7151 | 23 事实上， 今天我们的同事问了我同样的问题， 有一个问题我没有提到， 这个查询并
不能反映那些已经被踢出plan cache 里的query plans， 但是对于production
environment,大多数情况下，plan cache 是有可能保存一个小时内的plan cache. |
|
c*********n
发帖数: 128 | 24 Thanks for the reply!
So you mean there is no way to make a table containing the merged TBL_1 and
TBL_2?
I tried the following revised sentence (added "INTO TBL_MergeResult" in the
first line), but it didn't work and gave msg "An action query cannot be used
as a row source".
select b.field_x, b.field_y INTO TBL_MergeResult
from TBL_2 b left join TBL_1 a
on b.field_x = a.field_x
UNION ALL
select a.field_x, a.field_y
from TBL_1 a
where not exists (select 1 from TBL_2 b where a.field_x = b.field_x) |
|
c*********n
发帖数: 128 | 25 But no way through query?
I tried adding "INTO TBL_MergeResult" to the first line of your code (see my
previous reply) but it did not work. |
|
h******l
发帖数: 422 | 26 Try
SELECT ...... INTO TBL_MERGERESULT
What is your purpose of using this query? Why can't you do Update then
Insert? Can you please specify your problem so I can help you?
my |
|
c*********n
发帖数: 128 | 27 That works! Thanks so much!
My purpose is to merge (update existing and insert new) table2 into table1.
I did the job using the three queries in the first post but thought it's
kinda stupid to do it that way. |
|
b******y
发帖数: 139 | 28 I have a recordset ordered by CusipCol, IDCol like this:
CusipCol IDCol ValueCol
1C 1 A
2C 2 B
3C 1 A
3C 1 Null
4C 1 A
4C 2 Null
5C 1 A
5C 2 B
6C 1 Null
6C 2 B
7C 1 Null
7C 2 Null
I need write a oracle query to get the fol |
|
c**t
发帖数: 2744 | 29 不要轻易说没办法。我觉给你一个纯query吧：
with TEMP as (
select CusipCol, IDCol, ValueCol,
row_number() over ( partition by CusipCol order by CusipCol) as RN,
count(CusipCol) over (partition by CusipCol order by CusipCol) as CNT
from test)
select CUSIPCOL, IDCOL, VALUECOL from TEMP
where CNT=1
UNION ALL
select CUSIPCOL, IDCOL, VALUECOL from TEMP WHERE CNT>1 and RN=1 and VAlueCol
is NOT NULL
UNION ALL
select A.CUSIPCOL,
decode(a.VALUECOL, null, decode(B.ValueCol, null, A.IDCOL, B.IDCol),
a.IDCOL) as IDCOL, |
|
c**t
发帖数: 2744 | 30 You may use LAG to simplify the query
VAlueCol |
|
c***c
发帖数: 6234 | 31 谢谢回复。
那些错误的CI确实不在ci table里。但按你的query，确实返回0 row呀。
我用了笨办法
select i.incident_id, i.ci_name, ci.ci_name
from incidents i, configuration_items ci
where ci.ci_name (+)= i.ci_name
and ci.ci_name is null |
|
G*******l
发帖数: 601 | 32 请教在ACCESS query中, 如何count 每个有ID几个consecutive/continuous date for
each task?
JOB ID Date
TASK1 A 4/12/2009
TASK1 A 4/13/2009
TASK1 A 6/2/2009
TASK1 A 6/3/2009
TASK1 A 6/4/2009
TASK2 A 6/5/2009
TASK2 A 6/6/2009
TASK2 A 6/7/2009
TASK2 A 6/8/2009
TASK2 A 6/9/2009
TASK2 |
|
b******y
发帖数: 139 | 33 I have a record set like below:
CloseDate Category Price
2009-12-09 C1 100.21
2009-12-09 C2 98.41
2009-12-09 C3 96.41
2009-12-08 C1 100.13
2009-12-08 C2 98.33
2009-12-08 C3 96.33
2009-12-07 C1 100.2
2009-12-07 C2 98.25
2009-12-07 C3 96.25
2007-10-05 C4 97.4
2007-10-04 C4 97.3
2007-10-03 C4 97.3
2007-10-04 C5 98.5
2007-10-03 C5 98.4
I am trying to write a query to get columns includin |
|
c**t
发帖数: 2744 | 34 a classical pivot query |
|
A*******t
发帖数: 40 | 35 脑子不大好使，这个问题咋也没想明白，上来请教一下高手：
我有一个table，大约是这个样子：
************************************
SKU APP SEQ
AT1001 1 1
AT1001 1 2
AT1001 3 6
AT1002 1 2
AT1002 3 1
AT1003 1 1
AT1004 1 2
AT1004 1 3
AT1004 3 1
AT1004 3 2
AT1004 3 3
AT1004 3 4
......
现在想用一个Query来生成一个类似下面的报表：
×××××××××××××××××××××××××××××××××××××
SKU APP1 APP3
AT1001 2 1
AT1002 1 1
AT1003 1 0
AT1004 2 4
......
实在想不明白该如何实现，请求方家则个，多谢！！！ |
|
a*z
发帖数: 294 | 36 I am a greenhand on SQL. Can anybody recommend some intro books on SQL query
books? Appreciate it! |
|
m****e
发帖数: 1197 | 37 我不太懂SQL，但是看起来这个QUERY不太优化。请提示一下，都是INNER JOIN，怎么改
？谢谢。
SELECT dbo.tblDim_Period.Code AS MnthYr, dbo.tblDim_Account.Code AS
Account, dbo.tblDim_Prism.Code AS Prism, dbo.tblDim_Facility.Code AS
Facility,
dbo.tblDim_Product.Code AS Product, CASE WHEN dbo.
tblDim_Product.Accrual IS NULL
THEN 'ACT_ACT' ELSE dbo.tblDim_Product.Accrual END AS
ProdAccrl, dbo.tblDim_Channel.Code AS Channel, dbo.tblDim_Collateral.Code AS
Collateral,
dbo.t |
|
j*****n
发帖数: 1781 | 38 have WITH (NOLOCK) hint
otherwise it pretty depends on your indexes on your join keys for such
simply query. |
|
l*******y
发帖数: 11 | 39 我的数据情况如下：
每个ID有多组重复测量的值，但是有的没有测量值
ID field1 field2 filed3
1
1 z
1 x y Null
2 a
2 Null b c
.
.
.
n
n e f
n d Null Null
想通过query得到每个field最后一个非空的数值，即：
ID field1 field2 filed3
1 x y z
2 a b c
...
n d e f
用total，last 得出的只能得到最后一组值：
ID field1 field2 filed3
1 x y Null
2 Null b c
n d Null Null
谢谢大家啦！ |
|
c***c
发帖数: 6234 | 40 我想把duration 分组，分为0-4，4-7，7-15，>15这么几组，看看每组共几个
能用一个query写出来吗。还是必须写4个？
select count(*)
from table1
group by duration
谢谢 |
|
c***c
发帖数: 6234 | 41 谢谢。google了pivot table
其实就是用case when then else end 语句。还是query不熟。最后我写成
select count(case when (nvl(t.resolved_time, nvl(t.close_time,sysdate))- t.open_time) <= 4 then t.id else null end) as "<4",
count(case when (nvl(t.resolved_time, nvl(t.close_time,sysdate))- t.open_time) >4 and (nvl(t.resolved_time, nvl(t.close_time,sysdate))- t.open_time) <7 then t.id else null end) as "4-7",
count(case when (nvl(t.resolved_time, nvl(t.close_time,sysdate))- t.open_time) >=7 and (nvl(t.resolved_time, nvl(t.c |
|
j***3
发帖数: 142 | 42 【 以下文字转载自 Statistics 讨论区 】
发信人: j1123 (2134), 信区: Statistics
标 题: [Mysql] how to return NULL count in group by query
发信站: BBS 未名空间站 (Wed Jun 30 09:45:08 2010, 美东)
I have a table like this:
color size fruit
red big apple
red small apple
green big apple
yellow small orange
red small orange
when I do:
select color, size, count(*) group by color, size;
I got:
red big 1
red small 2
green big 1
yellow small 1
but what I need is to also return the NULL group count su |
|
i****a
发帖数: 36252 | 43 basic idea is to use row_number and self joining tables. if you use CTE,
here is an example
with cte as
(
select ROW_NUMBER() over (order by tb1.colDate) as rn1, *
from tableTest2 tb1
)
select *
from cte
inner join (select ROW_NUMBER() over (order by colDate) as rn2, * from
tableTest2) tb2
on cte.rn1 = tb2.rn2 - 1 and cte.attr <> tb2.prev
if you don't want to use CTE or not on SQL server 2005 or later, then put
result from the 1st query into a temp table |
|
c**t
发帖数: 2744 | 44 Oracle (10g), table A (id, Node, ...), where id, Node combination is unique.
How to query if Node1, Node2, ... with id1 exists where Node1,Node2.., id1
are parameters (inputs)? |
|
g********e
发帖数: 9 | 45 有一个table A如下：
ID status
1 A
2 B
3 C
4 A
5 D
6 C
7 E
8 F
想通过query得到如下结果：
Status count Count%toTotal
A 2 25%
B 1 12.5%
C 2 25%
D 1 12.5%
E 1 12.5%
F 1 12.5%
Total 8 100%
这个querey应该怎样写？SQL sever 2005
谢谢！ |
|
m******9
发帖数: 104 | 46 Any suggestion on how to query MS Access in command line? Importing to MS
SQL
server is not the option.
Thank you. |
|
B*****g
发帖数: 34098 | 47 这个和在access里run query有什么不同？
MS |
|
i****a
发帖数: 36252 | 48 I have free version of Toad for SQL Server. it has intellisense and object
drop down during coding, but doesn't have code formatting with free
version... blah... so I don't use it...
Recently got a copy of SQL Prompt from RedGate. It is an add-on to SSMS.
It does exactly what I needed, intellisense and code formatting. But it's
very sluggish, often the intellisense menu takes a second to response and
slows me down when I write simple queries.
I always hear good things about Toad, specially the q |
|
g***l
发帖数: 18555 | 49 SELECT max(Field2) FROM Table GROUP BY Field3 出来时一个SET的VALUES，你是没
显示FIELDS3, 不是一个确定的数值，所以不行,因为没有FIED3显示，你的INNER QUERY
出来没有意义，这个GROUP BY肯定是要放在外面的，BEIJING好像是对的。但每个FIELD3只能有一个RECORD,还不是你想要的 |
|
g***l
发帖数: 18555 | 50 保险起见Access里写两个QUERY吧
QUERY1: SELECT FIELD3, MAX(FIELD2) AS MAXFIELD2 FROM TABLE GROUP BY FIELD3
QUERY2: T.* FROM QUERY1 Q1, TABLE T WHERE FIELD1=1
AND Q1.MAXFIELD2=T1.FIELD2 AND Q1.FIELD3=T.FIELD3 |
|
