c***n
发帖数: 921 | 1 Thanks! I come up with this finally.
select a1,a2,value from (
select a1,a2,value row_number() over(partition by a1,a2 order by date
desc
) as rnber
from fctTbl ) end
where rnber=1 ; |
|
B*****g
发帖数: 34098 | 2 你太懒了。
select ...
from
(select ..., rank() over (partition by t1.ID order by t1.date desc) rn
from t1, t2
where t1.id = t2.id and t1.date <= t2.date)
where rn = 1 |
|
|
c*******e
发帖数: 8624 | 4 select *
from your_table
qualify row_number() over (partition by column1 order by column3 desc) = 1 ; |
|
i******c
发帖数: 9350 | 5 关键怎样identify是同一个人. 剩下的好办.
2个table:
employee table:
EID, name, address, phone
empHistory Table:
HID, EID, joinDate, leaveDate
select * from (
select e.eid, e.name, e.address, e.phone, h.joinDate, h.leaveDate, rank()
over(partition by e.eid order by h.joinDate desc) as rnk
from employee e
join empHistory h on e.eid = h.eid
)t
where rnk = 1 and leaveDate is not null |
|
u***t
发帖数: 3986 | 6 想了个办法:
select *
from (
select top 10 *
from (
select top 20(or 30, or 40...) *
from table
order by ...id
) tab order by ...id DESC
) tab1 order by ...id |
|
i*****w
发帖数: 75 | 7 Use Partition and if you have the ID indexed, it should be very fast. Try
the example here and you can change it according to your table structure.
declare @tbl Table(id INT identity, name varchar(30), isActive bit)
insert into @tbl values('Product One', 1)
insert into @tbl values('Product One', 1)
insert into @tbl values('Product One', 1)
insert into @tbl values('Product Two', 1)
insert into @tbl values('Product Two', 1)
insert into @tbl values('Product Three', 1)
insert into @tbl values('Produ... 阅读全帖 |
|
i*****w
发帖数: 75 | 8 UPDATE a
SET a.products_status = 0
FROM zen_products a
INNER JOIN
(
SELECT products_id, RANK() over (partition by products_name order by
products_id desc) rk from zen_products_description
) b
ON a.products_id = b.products_id
WHERE b.rk > 1 |
|
m*********y
发帖数: 389 | 9 Here are some questions I copied from online.. Obviously LouZhu is a lazy
ass... these questions are everywhere... :-)
SQL Interview questions
Below is a list of questions in this blog post so you can test your
knowledge without saying answers. If you would like to see questions and
answers please scrool down.
Question: What type of joins have you used?
Question: How can you combine two tables/views together? For instance one
table contains 100 rows and the other one contains 200 rows, have exac... 阅读全帖 |
|
b*****d
发帖数: 15 | 10 We have the following schema of a table:
create table Test (id number, name varchar2(32), desc varchar2(400));
create index index_test on Test (name);
Which of the following statements will invoke an index scan by oracle
execution plan
a) select * from test where name='name';
b) select * from test where name like 'name%';
c) select * from test where name like '%name';
d) select * from test where name like '%name%' |
|
B*****g
发帖数: 34098 | 11 ab
---
没事请勿往下看
憋了半天，还是抬个杠，desc是reserved word的，所以create table那个script会
fail |
|
v***e
发帖数: 2108 | 12 看到这道题好玩，答案是 ab是index scan，cd是table scan
两种SQL plan
再来一道题玩一下，如果Test table巨大无比，一定要用parallel query，
这两种parallel plan会有何不同呢（PX coordinator不算的话）？
不许查书。
发信人: bearkid (bearkid), 信区: Database
标 题: 一道面试题，求助
发信站: BBS 未名空间站 (Wed Dec 28 21:18:35 2011, 美东)
We have the following schema of a table:
create table Test (id number, name varchar2(32), desc varchar2(400));
create index index_test on Test (name);
Which of the following statements will invoke an index scan by oracle
execution plan
a) select * fro... 阅读全帖 |
|
y****9
发帖数: 144 | 13 ------
select stock_ticket, analyst_name, max(rating_date) Recentest_Rating, rating_
name
from stock
group by stock_ticket, analyst_name, rating_name
order by stock_ticket,analyst_name;
-----
The above query probably is not what the OP means. It gave the following
results:
For each stock, for each rating_name, the latest time when an analyzst made
the recommedation.
for example:
insert into stock values ('G','Abramson','6-Sep-2008','Hold');
insert into stock values ('G','Abramson','6-Sep-2009','... 阅读全帖 |
|
i****i
发帖数: 18 | 14 I have a question about T- SQL language.
I wrote a query to get the SUM of every line's Amount with the same
Initiative Key.
SELECT
[Initiative Key]
,[Amount]
,SUM ([Amount]) OVER (PARTITION BY ([Initiative Key])) AS SUM_AMOUNT
FROM [DataClean].[dbo].['Initiative Donations$']
ORDER BY SUM_AMOUNT DESC
Here is the result,
Initiative Key Amount SUM_AMOUNT
KBERFVSJRZIL 10 380
KBERFVSJRZIL 25 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZ... 阅读全帖 |
|
y****w
发帖数: 3747 | 15 select sum(value*power(10,(order-1))) from (select value, rownumber()over()
as order from version order by 1 desc)t |
|
T****U
发帖数: 3344 | 16 SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC |
|
w**********1
发帖数: 17 | 17 Need to replace Field_A with bottom layer of data in Field_B in Table_X.
Wanted to use the following -
UPDATE Table_X
SET Field_A=
(SELECT TOP(1) Field_B FROM Table_X ORDER BY Field_C DESC)
GO
However 'order by' is not allowed in sub-query. What could be the workaround
? Thanks!! |
|
j*******n
发帖数: 48 | 18 UPDATE Table_X
SET Field_A=
(
SELECT TOP(1) Field_B
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Field_C DESC) as row_num, Field_B
FROM Table_X) t
WHERE t.row_num = 1
) |
|
w*r
发帖数: 2421 | 19 此例中只有三行，所以答案的列是4(3+1)列，结果应该是3!=6行，如果有四行，答案是
5列 4!行
如果要求是答案中abc/abc在一列里面，那么就要求有RECURSIVE CALL 做character
sequence permutation
如果知道一共输入有多少列（列数确定）可以简单的做inner join
CREATE VOLATILE TABLE t (
KEY1 VARCHAR(1),
KEY2 VARCHAR(3)
) PRIMARY INDEX(KEY1)
ON COMMIT PRESERVE ROWS;
INSERT INTO t VALUES('A','GTL');
INSERT INTO t VALUES('B','GTL');
INSERT INTO t VALUES('C','GTL');
SELECT T1.KEY1,T2.KEY1,T3.KEY1,T1.KEY2
FROM T T1
INNER JOIN
T T2
ON
T1.KEY2 = T2.KEY2 AND
T1.KEY1 <> T2.KEY1... 阅读全帖 |
|
z***y
发帖数: 7151 | 20 You have SQL SERVER 2008 R2 which has been short of memory. Now you had
added memory and need to monitor to make sure SQL SERVER has enough memory,
which options are needed (Two options are needed):
a. run dbcc memorystatus
b. Check SQL Server Buffer Manager: Checkpoint Pages/Sec
c. Chck SQL Server Memory Manager: Target Server Memory
d. SQL Server Buffer Manager: Page Life Expectancy
e. run select * from sys.dm_os_wait_stats order by wait_time_ms desc;
答错不丢人。 要求不google， 五分钟作答。 第一个答对并且有具体说明的， 给1... 阅读全帖 |
|
n****f
发帖数: 905 | 21 你把四个表用 KEY 给JOIN 起来， 就形成一个完整的信息。
1. SELECT A.*, B.*, C.*, D.*
FROM A,B,C,D WHERE A.XX= B.XX ..... <= 这些您得自己去写。
暂时叫这个表是 W
在 W 里面， 你要把每人， 每山头的次数提取出来。
所以，
1. 中的 SQL 就应该CHANGE TO:
2. SELECT COUNT(*)-1 AS CT, CLIMBER.NAME AS CN, PEAK.NAME AS PN FROM
A,B,C,D WHERE A.XX= B.XX .....
GROUP BY LIMBER.NAME, PEAK.NAME
3. SELECT PN, SUM(CT) FROM
(SELECT COUNT(*)-1 AS CT, CLIMBER.NAME AS CN, PEAK.NAME AS PN FROM
A,B,C,D WHERE A.XX= B.XX .....
GROUP BY LIMBER.NAME, PEAK.NAME) X
GROUP BY PN
ORDER BY SUM(CT) DES... 阅读全帖 |
|
e****7
发帖数: 4387 | 22 -- CREATE TABLE STRUCTURE
IF OBJECT_ID('dbo.PEAK', 'U') IS NOT NULL DROP TABLE PEAK
GO
IF OBJECT_ID('dbo.CLIMBER', 'U') IS NOT NULL DROP TABLE CLIMBER
GO
IF OBJECT_ID('dbo.PARTICIPATED', 'U') IS NOT NULL DROP TABLE PARTICIPATED
GO
IF OBJECT_ID('dbo.CLIMBED', 'U') IS NOT NULL DROP TABLE CLIMBED
GO
CREATE TABLE PEAK
(
NAME VARCHAR(255),
ELEV INT,
DIFF INT,
MAP INT,
REGION INT
)
GO
CREATE TABLE CLIMBER
(
NAME VARCHAR(255),
SEX CHAR(1)
)
GO
CREATE TABLE PARTICIPATED
(
... 阅读全帖 |
|
e****7
发帖数: 4387 | 23 --CREATE TABLE #t (
-- ITEMID INT,
-- Date DATE
--)
--GO
--INSERT #t VALUES (1, '01/01/2012')
--INSERT #t VALUES (1, '02/01/2012')
--INSERT #t VALUES (1, '03/01/2012')
--INSERT #t VALUES (1, '04/01/2012')
--INSERT #t VALUES (2, '04/05/2012')
--INSERT #t VALUES (2, '03/06/2012')
--INSERT #t VALUES (2, '02/07/2012')
--INSERT #t VALUES (2, '02/8/2012')
WITH RECORDS AS (
select *, ROW_NUMBER() OVER (partition by ITEMID order by DATE desc) RN
from #t
)
SELECT ITEMID, [1] AS MostRecentDate1,... 阅读全帖 |
|
P********R
发帖数: 1691 | 24 (a) 在MS SQL Server里用这个JOIN的话，得出的结果应该会有重复（duplicate）的情
况，用IN（subquery)可以排除重复。
SELECT e1.id,
e1.sal
FROM employee AS e1
WHERE e1.sal IN
(SELECT e2.sal
FROM employee AS e2
WHERE e2.id <> e1.id)
ORDER BY e1.sal, e1.id;
(b) 用TOP 1, 倒着排序：
SELECT TOP1 WITH TIES
id,
sal
FROM employee
ORDER BY sal DESC, id; |
|
B*****g
发帖数: 34098 | 25 抄了一个，嘿嘿
WITH
sorted_requests as (
SELECT
member, from_date, to_date,
ROW_NUMBER() OVER (PARTITION BY member ORDER BY from_date, to_date
DESC) Instance
FROM
TABLE1
),
no_overlap(member, from_date, to_date, Instance, ConnectedGroup) as (
SELECT
member,
from_date,
to_date,
Instance,
Instance as ConnectedGroup
FROM sorted_requests
WHERE Instance = 1
UNION ALL
SELECT
s.member,
s.from_date... 阅读全帖 |
|
w*r
发帖数: 2421 | 26 SELECT * FROM INVENTORY
QUALIFY ROW_NUMBER() (ORDER BY PRICE DESC) = 1 |
|
c*****d
发帖数: 6045 | 27 真受不猪一样的队友了
给大家show一个SQL语句
validation_log表记录了每个app_id在时间validation_ts验证通过
类似
app_A, ts_1
app_A, ts_2
app_A, ts_3
app_B, ts_4
现在要获得2012/9/1以后每个app_id每个月的验证次数，类似
app_A, 2012/09, 10
app_A, 2012/10, 20
app_A, 2012/11, 20
...
app_B, 2012/09, 15
...
select app_id,to_char(max(validation_ts),'mon-yyyy') Month,count(*)
from validation_log
where validation_ts >= to_date('01-sep-2012','dd-mon-yyyy')
group by app_id,to_char((validation_ts),'mon-yyyy')
order by app_id,max(validation_ts) desc |
|
c*****d
发帖数: 6045 | 28 这个表还有一个archive表
3个月以前的数据在archive表
3个月内的数据在log表，两个表没有交集
老印队友这么写这个语句
还和我说为啥执行了一个小时也没返回结果
我和小伙伴都惊呆了
这需要啥样的智商呀
select app_id,to_char(max(validation_ts),'mon-yyyy') Month,count(*)
from validation_log a, validation_log_archive b
where a.validation_ts >= to_date('01-sep-2012','dd-mon-yyyy')
and b.validation_ts >= to_date('01-sep-2012','dd-mon-yyyy')
group by a.app_id,to_char((a.validation_ts),'mon-yyyy')
order by a.app_id,max(a.validation_ts) desc |
|
s******c
发帖数: 87 | 29 1. Join两个table，需要返回最大值的行，写了如下语句，但是返回的还是第一行，只
是返回的其中的一个最大值。
2.在语句上再加上一个删除冗余数据，该怎么加啊，就是当p.date-u.data_applied 小
于2天。
谢谢
SELECT j. *
FROM user_plans u2
INNER JOIN (
SELECT u.cancel, p.user_id, p.package_id, p.date, p.start_date, p.expiration
_date, p.coupon_id, MAX( u.date_applied ) AS latest
FROM user_plans u
INNER JOIN payments p ON p.user_id = u.user_id
AND p.coupon_id =23
GROUP BY u.user_id
)j ON j.user_id = u2.user_id
AND j.latest = u2.date_applied
GROUP BY j.user_id
ORDER BY j.latest DESC |
|
s**********o
发帖数: 14359 | 30 从最常用的SP开始研究，SQL SERVER的话RUN个DMV
SELECT
DatabaseName = DB_NAME(st.dbid)
,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid)
,StoredProcedure = OBJECT_NAME(st.objectid,dbid)
,ExecutionCount = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL
AND cp.objtype = 'proc'
GROUP BY
cp.plan_handle
,DB_NAME(st.dbid)
,OBJECT_SCHEMA_NAME(objectid,st.dbid)
,OBJECT... 阅读全帖 |
|
l******b
发帖数: 39 | 31
俺来班门弄斧一下, 看这样行不行?
为简化起见, 根据OP的要求, 先建两张表,一张放数据, 一张放参数.
CREATE TABLE t7(
CATEGORY INT,
ID INT,
num int
);
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,1,2) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,2,1) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,3,6) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,4,3) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,5,5) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(2,1,2) ;
INSERT INTO t7(CATEGORY, ID, num) VALU... 阅读全帖 |
|
l******b
发帖数: 39 | 32
sal
对？
You are right. The above code returns the bottom 3 instead of top 3.
However, if you want to solve the problem by thinking like rank>=(total-3),
you're going to a dead end.
Simply changing the above code
AND s1.sal >= s2.sal
to
AND s1.sal <= s2.sal
will give you the top 3.
I changed the above code a litter bit(I think it's better put s1.id <>s2.id
condition, coz a row doen't have to compare with itself)
I ran the query against the sample table EMPLOYEES
on Oracle's sample s... 阅读全帖 |
|
l******b
发帖数: 39 | 33
sal
对？
You are right. The above code returns the bottom 3 instead of top 3.
However, if you want to solve the problem by thinking like rank>=(total-3),
you're going to a dead end.
Simply changing the above code
AND s1.sal >= s2.sal
to
AND s1.sal <= s2.sal
will give you the top 3.
I changed the above code a litter bit(I think it's better put s1.id <>s2.id
condition, coz a row doen't have to compare with itself)
I ran the query against the sample table EMPLOYEES
on Oracle's sample s... 阅读全帖 |
|
d****n
发帖数: 12461 | 34 是每个dept的top 10？
如果是oracle，先join再row_number() over (partition by department_id order by
salary desc)挺好。 |
|
d****n
发帖数: 12461 | 35 select
department_id,salary
from
(select
t2.department_id,t1.salary,
row_number() over (partition by t2.department_id order by t1.salary desc)
salary_rank
from
employee_table t1,department_table t2
where
t1.employee_id=t2.employee_id
)
where salary_rank<=10 |
|
d***e
发帖数: 793 | 36 1.
select quid
from survey_log
group by quid
order by sum(case eventtype = 'answer' then 1 else 0 end)/count(*) desc
limit 1
2. 1/6 + 5/6*1/6
对吗？ |
|
A*******n
发帖数: 625 | 37 请教一个问题：
declare @cust table
(
Id int IDENTITY(1,1) NOT NULL,
Rateid int,
custId int
)
这个table有18000多个记录。
create table #rate
(
CustId int,
RateID int,
RateTypeId int,
EffectiveDate datetime,
ExpirationDate Datetime,
OrderIndex int
)
这个table有92000个记录，可是下面这个loop就很慢（要3-4分钟）：
select @count=COUNT(1)
from @cust
--------这个loop是找到每一个Cust Id的Rate Type IDs
while(@index<=@count)
begin
set @result=''
set @custid=0
select @custid= custId from @cust ... 阅读全帖 |
|
p*****u
发帖数: 310 | 38 请教以下SQL如何优化。我已经斗争了一天了，实在不太懂。其实就是一个很大的表要
left join 一个小表。所有相关的列都index了。不胜感谢。
select
report.type,
report.categoryId,
max(report.submitTime) as submitTime,
count(report.categoryId) as occurrences,
category.description
from
77_report report
LEFT JOIN
categoryinfo category ON category.categoryid = report.categoryid
AND category.skuid = 77
where
1 = 1
and report.submitTime > (CURDATE() - INTERVAL 7 DAY)
group by categoryId
order by submitTime desc... 阅读全帖 |
|
m**********4
发帖数: 66 | 39 我刚开始学sql，在sqlzoo上被几个题目弄糊涂了，不知道是题目理解得不对，还是
syntax不对，出来的结果和正确答案就是不一样。
more join operation里的14和15题
1. 我写的如下。我的理解是同一个演员不可能在一部戏里有两个ord，计算一个演员的
ord次数，就可以知道他/她 starring roles的次数。这个query出来的行数比答案多。
select name from actor join casting
on actor.id=casting.actorid
group by name
having count(ord)>=30
order by name
2. 我写的如下。我的query出来的结果有几行不对，比如答案里有midnight express，
我的结果没有。
select t1.title, count(t3.actorid) from movie t1
join casting t3
on t1.id=t3.movieid
where t1.yr=1978
group by t1.title
order by count(t3.... 阅读全帖 |
|
d******i
发帖数: 7160 | 40 简单说table(SC) 就两列:科目(cid)和分数(score)
因为MySql不让top 3，只得LIMIT 3.
SELECT score FROM SC SC0
WHERE score IN (
SELECT score FROM SC
WHERE cid=SC0.cid
ORDER BY score DESC
LIMIT 3
) T2
;
结果报错：
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/
ALL/ANY/SOME subquery'
咋办呢？
请指教。谢了！ |
|
w****w
发帖数: 521 | 41 with cte_table as (
选择 id,company1,company2, 1 AS level, company1+' '+company2 as path
从 your_table
union all
选择 a.id,a.company1,b.company2,a.level+1,a.path+' '+b.company2
从 cte_table a join your_table b a.company2 = b.company1
哪里 a.level < 100
)
选择 top 1 *
从 cte_table
哪里 level <100
顺序 by level desc; |
|
s******v
发帖数: 4495 | 42 好像就是一个bgp neighbor, fwd所有的updates，我见过那个isp的ios cfg，
description写的是 nei desc arbor |
|
t*******y
发帖数: 57 | 43 【 以下文字转载自 Database 讨论区 】
【 原文由 tribology 所发表 】
这几天真是见了鬼了
在Linux下单独写个standalone的java程序用JDBC访问oracle一点问题都没有
换写成servlet来通过JDBC访问oracle竟然只能做"select .."这样的sql访问
用什么insert/update/desc之流的sql命令通通给我出错,
说什么java.sql.SQLException: SQL string is not Query
ft,明明直接把那些sql命令放入sqlplus执行和放在standalone的java程序里面
执行一点问题都没有.google了半天没有啥结果.
这个到底啥原因有人知道么?难道用servlet调用jdbc数据库还有权限限制?
用perl写的cgi调用数据库也一点问题都没有呀. |
|
t********k
发帖数: 808 | 44 oracle.sql.ArrayDescriptor desc =
oracle.sql.ArrayDescriptor.createDescriptor("VARRAY1", con);
oracle.sql.ArrayDescriptor descriptor =
oracle.sql.ArrayDescriptor.createDescriptor("TYPEVARCHARARRAY",con);
上面二句都会抛出java.lang.ClassCastException异常为什么呢?
要对Oracle的连接有什么要求?
我完全是按照老一辈无产阶级革命家Tom的指导下做的
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:8908169959941
为什么别人能通过,我确有问题呢?
其中二个类型的定义是这样的
create type TYPEVARCHARARRAY as varray(10) of varchar2(20);
CREATE TYPE varray1 AS VARRAY(10) OF |
|
f*******4
发帖数: 345 | 45 Try this:
select c.customerName, c.purchaseDate, p.amount from Customer c left join c.
purchases as p group by c.customerName order by c.purchaseDate desc |
|
l***i
发帖数: 289 | 46 那些fancy的search可以作为get 的参数啊
例如search?ignorecase=true&order=desc&date_blah
REST主要强调啥咚咚都是资源，只不过传统web是人consume的资源，web service是计
算机consume的资源，HTTP协议本身就足够了。 |
|
|
t*******y
发帖数: 57 | 48 【 以下文字转载自 Database 讨论区 】
【 原文由 tribology 所发表 】
这几天真是见了鬼了
在Linux下单独写个standalone的java程序用JDBC访问oracle一点问题都没有
换写成servlet来通过JDBC访问oracle竟然只能做"select .."这样的sql访问
用什么insert/update/desc之流的sql命令通通给我出错,
说什么java.sql.SQLException: SQL string is not Query
ft,明明直接把那些sql命令放入sqlplus执行和放在standalone的java程序里面
执行一点问题都没有.google了半天没有啥结果.
这个到底啥原因有人知道么?难道用servlet调用jdbc数据库还有权限限制?
用perl写的cgi调用数据库也一点问题都没有呀. |
|
t**********s
发帖数: 930 | 49 谢谢!
我又加了相同成绩并列名次的处理.
SELECT S.ID_STUDENT, S.ID_CLASS,S.GRADE, COUNT(*) AS RANK FROM STUDENTS AS S
LEFT OUTER JOIN STUDENTS AS S1 ON S.ID_CLASS = S1.ID_CLASS AND (((S.GRADE =
S1.GRADE) AND (S.ID_STUDENT=S1.ID_STUDENT)) OR (S.GRADE < S1.GRADE) ) GROUP
by S.ID_CLASS, S.GRADE DESC, S.ID_STUDENT; |
|
