f*******h
发帖数: 53 | 1 来自主题: JobHunting版 - 问道sql SQL SERVER CTE:
with cte as(
select empid
from employess
where empid=34
union all
select e.empid
from employess as e
where e.supervisor=cte.empid
)
select * from cte |
|
y***t
发帖数: 644 | 2 SELECT E.EMPID AS EMPID, E.NAME AS NAME, LOCATION, E.SALARY+B.NBONUS AS
TOTAL FROM EMPLOYEES E INNER JOIN BONUS B ON E.EMPID = B.EMPID ORDER DESC BY
TOTAL LIMIT 1 |
|
f*****e
发帖数: 5177 | 3 Say I have two tables, Employee and PayRate. Employee has a primary key
EmpID which is a foreign key to PayRate. I need to update any matched
employees whose pay rate is greater than 1. I have to consider both
performance and mutual lock. Here are two solutions:
1. SELECT then UPDATE
select @cnt=count(*) from employee where name=@name and dob=@dob and empid
in (select empid from payrate where rate >1)
if @cnt > 1
begin
update employee set status = 1 where name=@name and dob=@dob and empid in
( |
|
gy
发帖数: 620 | 4 下面两句我没看出啥区别来, 谁说说??
(一个先判断一下, 然后update, 另一个直接update.)
if @cnt > 1
begin
update employee set status = 1 where name=@name and dob=@dob and empid in
(select empid from payrate where rate > 1)
2. UPDATE directly
update employee set status = 1 where name=@name and dob=@dob and empid in
(select empid from payrate where rate > 1) |
|
h*********o
发帖数: 151 | 5 现有这样一段code，目的是：display the names of all navigators who are also
managers.
proc sql;
select lastname,firstname
from sasuser.staffmaster
where 'NA'=
(select jobcategory
from sasuser.supervisors
where staffmaster.empid = supervisors.empid);
我不明白的是 where clause里面的 'NA'= 是什么意思啊？作什么用的呢？为什么不写
成这样呢：
proc sql;
select lastname,firstname
from sasuser.staffmaster
where staffmaster.empid in
(select supervisors.empid
from sasuser.supervisors); |
|
c*****h
发帖数: 166 | 6 来自主题: JobHunting版 - 问道sql 一个表结构是这样的
EMPLOYEES
__________________________________________
EMPID NAME SUPERVISOR LOCATION SALARY
------------------------------------------
34 Amy NY 110000
17 Ben 34 TN 75000
5 Chris 34 TN 80000
10 Don 5 HI 100000
问题是给一个EMPID, 怎么样找出所有向这个人汇报的人 比如给EMPID 34,要返回34,17
,5,10
就写SQL的话怎么做？多谢 |
|
l****c
发帖数: 782 | 7 EMPLOYEES BONUS
__________________________________________ _____________
EMPID NAME SUPERVISOR LOCATION SALARY EMPID NBONUS
------------------------------------------ -------------
34 Amy NY 110000 17 5000
17 Ben 34 TN 75000 10 2000
5 Chris 34 TN 80000 34 5000
10 Don 5 HI 100000 ...
1... 阅读全帖 |
|
a********r
发帖数: 60 | 8 The following SAS program is submitted:
data WORK.ACCOUNTING;
set WORK.DEPARTMENT;
length EmpId $6;
CharEmpid=EmpId;
run;
If data set WORK.DEPARTMENT has a numeric variable EmpId,
which statement is true about the output dataset?
A.
The type of the variable CharEmpid is numeric.
B.
The type of the variable CharEmpid is unknown.
C.
The type of the variable CharEmpid is character.
D.
The program fails to execute due to errors.
why the answer is D instead o... 阅读全帖 |
|
c********9
发帖数: 53 | 9 Select EMPID, NAME, LOCATION, SALARY+BONUS as HSalary from EMPLOYEES inner
join Bonus on EMPID Where HSalary=max(SALARY+BONUS) group by LOCATION; |
|
a****i
发帖数: 14 | 10 求detail.
我能想出来的是用pl/sql建cursor, 然后loop的时候assign mgrid=empid |
|
s*******r
发帖数: 769 | 11 68 D：look at the line:
length EmpId $6; |
|
s***1
发帖数: 343 | 12 非常感谢你给的SUGI26链接！
但是我在自己机器上用sas9.1试了里面的example，用的column input，试了missover
和truncover，但是结果竟然都是一样的，都是：
Obs lastn Firstn Empid Jobcode
1 LANGKAMM SARAH E0045 Mechanic
2 TORRES JAN E0029 Pilot
3 SMITH MICHAEL E0065
4 LEISTNER COLIN E0116 Mechanic
5 TOMAS HA... 阅读全帖 |
|
k****1
发帖数: 123 | 13 EmpId 是numeric，不能define Char length? |
|
P*****J
发帖数: 1745 | 14 不错不错。
Least Flycatcher听见唱歌没有？Empids我一般只能靠声音辨别，不过如果是Least的
话，这个eye ring好像不够宽。还有别的照片没有，换个角度看看嘴和primary
projection长短？
Flycatcher |
|
P*****J
发帖数: 1745 | 15 真是过奖了。因为不打鸟，所以我在找鸟上经常偷懒。听见声音就算数，说实在的
Empids长什么样我没有怎么仔细研究过，大部分时间都花在找更好看的鸟上了 -_-|| 。
我对着Kaufman Field Guide to Advanced Birding好好看了一下。你打到的这只鸟从
背部颜色，喉咙和头部的反差，wingbar颜色和形状细节确实都和Least Flycatcher最
相近。Eye ring和primary projection(由于角度，不是特别容易判断)我还是不太拿得
准，不过我觉得这个很可能就是Least Flycatcher了。恭喜打到新鸟啊！ |
|
s*****2
发帖数: 3103 | 16 哈哈，这本书我也有。我在这个鸟身上花了好几个钟头，这本书关于empid的那一章也
仔细看了很久，最后觉得有60-70%的把握吧（其它30-40%是Acadian Flycatcher），应
该差不多了。我的List只计拍到的、基本拿得准的，70%可以算是我的下限，已经极为
保守了。这个就先算了吧，已经老老实实地做好功课了。
。 |
|
P*****J
发帖数: 1745 | 17 嗯，很像。
不好意思，Empids我没有仔细研究过，辨别基本靠耳朵。 |
|
