f*****e
发帖数: 5177 | 1 Here is the table:
Col1, Seqno, ...
A, 1, ...
A, 2, ...
A, 3, ...
B, 1, ...
C, 1, ...
(Col1 + seqno) is the primary key.
When I use LINQ to insert a new record, I need to calculate next seqno. For
example, if I want to insert a new row with value A in col1, the seqno will
be 4.
It is not hard to do the calculation. But the problem is that I need to lock
the table when I do that since i don't want another connection to get the
same seqno. Is there a way to do that? |
|
l*****k
发帖数: 587 | 2 An issue that must have an elegant and simply solution.
I am trying to use transpose on a dataset, however how do
I specify all the variables?
if the are named col1, col1, etc, it is ok to use: var col1-col99
however what if they have random names, I have been trying to export
the data to excel, copy the header(variable name), then paste it back
to sas like
proc transpose data=original out=out;
var test1 result2 test2 result2........;
run;
is there a way to do this without specifying all |
|
b*****e
发帖数: 223 | 3 数据有 500 columns，大多数是 numeric，个别是 char。其实只需要其中十个左右
columns。我想这样弄的，试图把所有 columns 都读成 char，但是不行。有没有什么
好办法？我不想把 char column 一个个人为找出来单独列出来读，因为其实大部分都
用不到。
或者，有什么只读我需要的那些 columns 的读数据的方法?以前没读过这么多列的数据
，没经验
data ALLPAGE;
infile "\....\My Documents\MYDATA.txt" delimiter='09'x
firstobs=4 obs=1410 dsd lrecl=10000 missover;
input COL1-COL500 ; /* 这样 char col 都是空白 */
input COL1-COL500 $ ; /* 这样不行？ */
input COL1-COL222 COL223 $ ..... COLxxx-COL500; /* 嫌麻烦 */
run; |
|
k*******a
发帖数: 772 | 4 data a;
array col{5} $;
do i=1 to 5;
input col{i} $ @;
if input(col{i},8.) ne . then col{i}=0;
end;
datalines;
a34 33 34 bbd 56.5
bbb 33 2 ddf 12
;
proc print data=a;run;
proc transpose data=a out=b;
var col1-col5;run;
data b;
set b;
if col1 ne 0;
proc print data=b;run;
proc transpose data=b out=c(drop=_name_);
var col1-col2;
run;
proc print data=c;run; |
|
y*******1
发帖数: 18 | 5 71. A SAS program is submitted and the following is written to the SAS log:
15 %let col1 =dept; 16 %let data = company; 17 18 proc sql; 19 select * 20
from &data, employee 21
where &data.&col1 = employee.name; 22 quit; ERROR : The following columns
were not
found in the contributing tables: companydept. What caused the error?
A.The data set COMPANY does not contain the variable DEPT.
B.There should be a second period after &DATA in the WHERE clause.
C.There should be two ampersands in front of D... 阅读全帖 |
|
o****o
发帖数: 8077 | 6 作T检验，只需要三个统计量嘛，N,MEAN,STD
先求summarized statistics，再用BY-processing。跑得时间不见得少，不过至少不会
崩溃。而且这个用BY-PROCESSING可以很容易并行，最后合并结果也很容易。
134
135
136 options fullstimer;
137 data class;
138 set sashelp.class;
139 array col{20000};
140 do j=1 to dim(col); col[j]=rannor(0); end;
141 drop j;
142 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 20005 variables.
NOTE: DATA statement used (Total process time):... 阅读全帖 |
|
o****m
发帖数: 633 | 7 啥时候checking Bonus变成 200了？ 记得是150的。 觉得有用的，打赏下咯。
http://slickdeals.net/f/4341304-Chase-200-Cash-Bonus-for-New-Ch
Chase $200 Cash Bonus for New Checking Account
You can use this link to apply online or request to email a coupon.
For new checking customers, open a Chase Total CheckingSM account with a $
100 opening deposit, set up direct deposit and get a $200 cash bonus.https:/
/www.chase.com/ccp/index.jsp?pg_name=ccpmapp/shared/marketing/page/17455_
get200_now&ID=0000014036&MSC=COL1&jp_cmp=rb/6559... 阅读全帖 |
|
a***n
发帖数: 1616 | 8 I didn't know how to use variable binding.
but my sql statement is quite simple:
sql = "select col from table where col1='" + Javastring + "'";
statement.executeQuery(sql);
sql = "update table set col1='" + JavaString + "' where col2='" + s2 + "'";
statement.executeUpdate(sql);
这样做了若干次后(每次都关闭resultset,但statement一直不关),就耗干内存了...
我试了下,如果每次statement运行完就关闭,下次用的时候再create新statement就好了.
我想是因为我statement一直没关的原因吧? |
|
n********a
发帖数: 68 | 9
PreparedStatement ps = conn.prepareStatement("select col from table where
col1=?");
ps.setString(1, "foo");
ResultSet rs = ps.executeQuery(sql);
// Now iterate through the ResultSet.
// After you finished iteration, close ResultSet object.
rs.close();
// Don't close ps yet if you have another variable to bind
// You can reuse the PreparedStatement.
ps.setString(1, "bar");
rs = ps.executeQuery(sql);
PreparedStatement ps = conn.prepareStatement("update table set col1=? where
col2=?");
ps.setStrin |
|
B*****g
发帖数: 34098 | 10 SELECT col1, col2, col3, COUNT(1)
FROM tab1
GROUP BY ROLLUP(col1, (col2,col3)) |
|
j*****n
发帖数: 1781 | 11 some rules for you to start
rule 1. avoid table scan... check your execution plan by hit ctrl+l
rule 2. avoid CASE...THEN... in select statement
rule 3. avoid NOT IN, NOT EXIST
rule 4. use INNER JOIN when possible instead of LEFT/RIGHT OUTER JOIN
rule 5. use UNION ALL when possible against UNION
rule 6. avoid function against columns in WHERE clause
rule 7. use ISNULL() instead of OR in WHERE clause
e.g. WHERE col1 = @var1 OR @var1 IS NULL
can be written to
WHERE col1 |
|
B*****g
发帖数: 34098 | 12 Oracle 9i
准备run
SELECT ...
FROM table1 a
WHERE a.col1 IS NULL
在col１上怎么建index?
已经知道
create ind_tab1_col1 ON table1( NVL(col1, 'NULL') )
极其不喜欢.
Thanks |
|
B*****g
发帖数: 34098 | 13 CREATE TABLE NewTable AS
SELECT *
FROM ( SELECT COL1,COL2 FROM Table1
UNION ALL
SELECT COL3,COL4 FROM Table2)
or
INSERT INTO NewTable
SELECT *
INTO NewTable
FROM ( SELECT COL1,COL2 FROM Table1
UNION ALL
SELECT COL3,COL4 FROM Table2) |
|
M***7
发帖数: 2420 | 14 guys, thanks a lot .
Finally I used the following query
=====================
INSERT INTO NewTalbe
SELECT col1, col2 FROM TABLE1
UNION ALL
SELECT col3 col4 FROM TABLE2
======================
It woks.
If I use
=================
CREATE TABLE newtable AS
SELECT *
FROM (SELECT col1, col2 FROM TABLE1
UNION ALL
SELECT col3 col4 FROM TABLE2)
===================
It still did not work.
Could anyone explain it a little bit detail for me? I am really a rookie in
SQL.
Thanks. |
|
c*******e
发帖数: 8624 | 15 col1 = A, B这个column
col2 = 另外一个
select col1 ,
max(col2) ,
(sum(col2) - max(col2)) * 1.00 / max(col2)
from your_table
group by 1
order by 1 ; |
|
o******5
发帖数: 29 | 16 select (a.date_1-b.date_2)/24
from
(select date_1
from table_1
where blah blah) a,
(Select date_2
from table_2
where blah blah) b
where a.col1=b.col1 |
|
c*****d
发帖数: 6045 | 17 单字段的primary key能保证该字段被index
对于复合PK，如果PK是(col1，col2)，没问题，查询可以用Index
如果PK是(col2，col1)，不行，查询不可以用该Index |
|
c*****d
发帖数: 6045 | 18 好，更精确的说：
单字段的primary key能保证该字段被index，但是oracle未必一定使用该Index
对于复合PK，如果PK是(col1，col2)，查询可以用Index，但是oracle未必一定使用该
Index
如果PK是(col2，col1)，oracle查询不用该Index，除非你加入hint
这下大家满意了吧 |
|
y****w
发帖数: 3747 | 19 你确认oracle中即使如select count(*) from tab where col1 = xx 也不会用到如(col2,col1)的索引？ |
|
e***e
发帖数: 168 | 20 do the following and post the plan :
1.
explain plan for
select count(*) from TA a where a.col1=601;
2.
set lines 140 pages 1000;
3. check the plan
@?/rdbms/admin/utlxpls;
Do you see full table scan? If so, try this:
1.
explain plan for
select count(*) from TA a where a.col1=601 and a.col2 like 'ABC%'
2. check the plan
@?/rdbms/admin/utlxpls;
Do you see index access path?
If so, use hint to enable index access path used in the second query.
explain plan for
select /*+ index(TA index_name) */ cou |
|
R*********r
发帖数: 225 | 21 IF (2>1)
SELECT 1 as col1 INTO #test
ELSE
BEGIN
IF OBJECT_ID(N'tempdb.dbo.#test') IS NOT NULL DROP TABLE #test
SELECT 2 as col1 INTO #test
END
上面这个语句报错：
Msg 2714, Level 16, State 1, Line 7
There is already an object named '#test' in the database.
把#test换成permenant table就没问题，请问这个怎么解决？ |
|
n********6
发帖数: 1511 | 22 Environment: Win2003, SQL2005,
Process: Load .xls by using SSIS Wizard (DB->Task->Import Data)
.xls File Structure: (col1, col2, ..., col8, col9)
Columns: col8:numbers; col9: numbers
First Line: xx, xx,... xx, xx (to set col8, col9 character)
Loading result:col1, col2, ... col8 (nvarchar), col9(float)
Question: Why col8 nvarchar while col9 float?
Thank you. |
|
B*****g
发帖数: 34098 | 23 叫你朋友参加CINAOUG 9月7号的活动
table1(id, col1)
table2(id, col2)
SELECT id, col1, null AS col2
from table1
union all
SELECT ID, null, col2
from table2 |
|
T****U
发帖数: 3344 | 24 My answer to Q3
select * from
((select col1, col2, col3, col4, col5 from sevencol)
minus fivecol)
Union
(fivecol minus
(select col1, col2, col3, col4, col5 from sevencol))
query |
|
b******t
发帖数: 10 | 25 have a table
col1 col2
a1 b11,b12
a2 b21,b22
...
want to make a new table as
col1 col2
a1 b11
a1 b12
a2 b21
a2 b22
...
how to do this in MySQL?
thanks |
|
B*****g
发帖数: 34098 | 26 select *
from table t1, table t2
where t1.col2 = t2.col2
and t1.col1 <> t2.col1
然后再用你上次问的问题的答案 |
|
c*****d
发帖数: 6045 | 27 b和c表中column不一样多，当然在select后面选取的column是一样多的
类似
select a.col1, a.col2, b.col3
union
select a.col1, a.col2, c.col5 |
|
n****f
发帖数: 905 | 28 这个任务可以直接完成， 不用借助 C#。
1. 在 Oracle 建立一个表：
CREATE TABLE oldmanpushca.DATE_TEST
(
COL1 VARCHAR2(100 BYTE),
COL_DATE DATE
)；
2. 用 NOTEPAD 在 C:\OracleLoader\ 盘建立了一个 CSV 文件：
C:\OracleLoader\DATE_TEST.CSV， 内容如下：
C1R1 TEXT 1, 2013/10/22
C1R2 TEXT 2, 2014/10/22
C1R3 TEXT 3, 2015/10/22
3. 用 NOTEPAD 在 C:\OracleLoader\ 盘建立了一个控制文件：
C:\OracleLoader\Para.CTL ， 内容如下：
LOAD DATA
infile 'C:\OracleLoader\DATE_TEST.CSV'
APPEND
INTO TABLE oldmanpushca.DATE_TEST
FIELDS TERMINATED BY ',' optionally enclosed by... 阅读全帖 |
|
A*******n
发帖数: 625 | 29 try this:
select *
from
(select col1,col2,col3, RANK() over (partition by col1 order by col3) as
rank
from tab1 ) t
where t.rank=1 |
|
s**********o
发帖数: 14359 | 30 我的SQL SERVER一般是这样的
select
t1.col1,
t2.col1,...
from
table1 t1 with (nolock)
left outer join
table2 t2 with (nolock) on
(
t1.col = t2.col
) |
|
h**u
发帖数: 1512 | 31 我有下列一组数据：
COL1 COL2 COL3 COL4
1 A1 A2 A3 NULL
2 A1 A2 A3 A4
3 B1 B2 B3 NULL
4 B1 B2 B3 B4
5 C1 C2 C3 NULL
6 C1 C2 C3 C4
我想如果count（COL1）>1 就只显示row2，4，6，除去null的1，3，5。该如何实现呢
？多谢了。 |
|
x******m
发帖数: 736 | 32 table1
id col1 col2 col3...
table2
col_id col_name
1 col1
2 col2
3 col3
现在想用sql生成table3，
id col_name col_value
其中id，col_vaule来自于table1，col_name来自于table2.
多谢。 |
|
x******m
发帖数: 736 | 33 errrrrrrrrrrrrrr
在初级的DBA，也不会这么命名吧。我只是简单说col1，并不是column1的header就是
col1.哪个DBA要是这么命名，估计离丢工作也不远了。。。。
这样说吧，table1的attributes在数据库中被分成好几类，比如，身高，体重属于body
index，心跳，血压属于health index（table2）。
要生成的table3，就是id attribute 在加上 attribute的类别。
其实这就是个sql问题，跟这些都无关。
实在不行，我就把table output出来，几行python code就搞定。就是想借这个机会看
看sql有没有好的解法。 |
|
n****f
发帖数: 905 | 34 这个任务可以直接完成， 不用借助 C#。
这种方式容许你导入很大（1G，10G。。）的 CSV 文件。
1. 在 Oracle 建立一个表：
CREATE TABLE oldmanpushca.DATE_TEST
(
COL1 VARCHAR2(100 BYTE),
COL_DATE DATE
)；
2. 用 NOTEPAD 在 C:\OracleLoader\ 盘建立了一个 CSV 文件：
C:\OracleLoader\DATE_TEST.CSV， 内容如下：
C1R1 TEXT 1, 2013/10/22
C1R2 TEXT 2, 2014/10/22
C1R3 TEXT 3, 2015/10/22
3. 用 NOTEPAD 在 C:\OracleLoader\ 盘建立了一个控制文件：
C:\OracleLoader\Para.CTL ， 内容如下：
LOAD DATA
infile 'C:\OracleLoader\DATE_TEST.CSV'
APPEND
INTO TABLE oldmanpushca.DATE_TEST
FIELDS TERMINATE... 阅读全帖 |
|
d******e
发帖数: 117 | 35 我有一个文本文件1.txt如下：
col1 col2
col1 col2
......
在BASH代码里，我想把1.txt的column 1存入一个数组（或list)里，请问
有什么办法？ |
|
c*********e
发帖数: 16335 | 36 表a 加一个column: lasttimechanged
写个stored procedure,里面写2个query:
insert * into table1 + year() + month() + day() + hour() + min()
select * from A
where lasttimechanged >= (min()-10min)
and lasttimechanged <= getdate();
--有时候，即使是update,也可能新数据和老数据完全相同，所以也要加上下面这个sql:
select col1,col2,col3 (不包括column lasttimechanged) from table1 + year() +
month() + day() + hour() + min()
except
select col1,col2,col3 (不包括column lasttimechanged) from table1 + year() +
month() + day() + hour() + (min()-10min)
每1０分钟运行一次这个s... 阅读全帖 |
|
o******6
发帖数: 538 | 37 ☆─────────────────────────────────────☆
davfox121 (davfox) 于 (Sat Mar 7 08:57:42 2009) 提到:
假设有col1, col2, col3-----coln, 这儿n 是一个变量，不是常数， 我想把这几个
colume合成一个colume，请问如何用macro做？（不能用col1||col2||col3---,因为不
知道n是多少）
我想用循环，%do i=1 %to %n %by 1; %let finalcol=&finalcol||col&i; %end
但是好像不认col&i
是不是用proc sql 可以， 象
select * into:ary1-:ary&n from dataset where obs=&i;
%let finalcol='';
%do i=1 %to %n %by 1;
%let finalcol=finalcol||ary&i;
%end;
有谁还有好的方法吗？
还有，要是几个colume的type 不一样的话（例如如果有numeric)， 该如何处理呢？ |
|
d*******o
发帖数: 493 | 38 *****Request: 试图把所有 columns 都读成 char，但是不行。有没有什么
好办法？******;
data one;
infile whatever;
informat col1-col500 $varying.;
input col1-col500;
run;
***Tested**********; |
|
s******y
发帖数: 352 | 39 DATA HAVE;
INFILE CARDS TRUNCOVER;
INPUT A B C D;
ROWID=_N_;
CARDS;
1
7
3 3
2 5
. 5
. 5 3
. . 4 5
. . 5 8
. . 2 3
. . . 1
;
RUN;
PROC TRANSPOSE DATA=HAVE OUT=HAVE1(WHERE=(NOT MISSING(COL1)));
BY ROWID;
VAR A B C D;
RUN;
PROC SORT DATA=HAVE1;
BY _NAME_ ROWID;
RUN;
DATA HAVE2;
SET HAVE1;
BY _NAME_;
ID+1-(FIRST._NAME_)*ID;
RUN;
PROC SORT DATA=HAVE2;
BY ID _NAME_ ;
RUN;
PROC TRANSPOSE DATA=HAVE2 OUT=WANT(DROP=_NAME_ ID);
BY ID ;
VAR COL1;
ID _NAME_;
RUN; |
|
o****o
发帖数: 8077 | 40 think out of box, you can:
first transpose, then use PROC FREQ:
proc format ;
value $charmiss
' '='missing'
other='Non-Miss'
;
run;
data test;
array _c{*} $ c1-c5;
do id=1 to 20;
do j=1 to dim(_c);
if ranuni(5555)<0.2 then _c[j]=' ';
else _c[j]='A';
end;
output;
drop j;
end;
run;
data testv/view=testv;
set test;
id=_n_;
run;
proc transpose data=test out=test_t;
by id;
va... 阅读全帖 |
|
k*******a
发帖数: 772 | 41 你可以用array，也可以用proc transpose
如果用transpose你可以：
data b;
set a;
ID=_N_;
run;
proc transpose data=b out=c;
var var1-var4;
by ID;
run;
proc sort data=c;
by ID descending col1;
run;
data d(keep=newvar);
set c;
by ID descending col1;
if first.ID;
newvar=_name_;
run; |
|
d*******8
发帖数: 321 | 42 data _null_;
set d;
file "a.txt";
input col1 col2 。。。;
请问一个小问题，现在需要重复拷贝相同数据到同一文件内，如何把数据重复100遍写
到文本文件。
data _null_;
do i=1 to 100;
set d;
file "a.txt";
input col1 col2 。。。;
end; |
|
i******r
发帖数: 861 | 43 有数据A:
dataset A
col1 col2
a b
a b
c d
c d
c d
c d
x y
x y
w h
s k
m j
需要给duplicate加flag：第一个为Y，其余的为N，没有duplicate就为空，
最后的数据B 应该如下：
dataset B
col1 col2
a b Y
a b N
c d Y
c d N
c d N
c d N
x y Y
x y N
w h .
s k .
m j .
用sql怎么写？ |
|
i******r
发帖数: 861 | 44 为碰到的问题是：
data A
col1 col2
a b
b a
c d
d c
q r
r q
需要把（a b），（c d），（q r）标上Y，反过来的自动标N，即
col1 col2
a b Y
b a N
c d Y
d c N
q r Y
r q N
怎么用SQL语言来实现它？（只用SQL）谢谢。 |
|
c**i
发帖数: 6973 | 45 (1)
(a) In any event, Americans won 上甘岭战役 Battle of Triangle Hill
http://en.wikipedia.org/wiki/Battle_of_Triangle_Hill
(Oct 14-Nov 25, 1952; Strength: UN Infantry Unknown, China Infantry 50,000 [
8]; Casualties and losses: US 365 killed 1,174 wounded 1 captured [10],
China 4,838 killed 6,691 wounded [13];
The main combatants were two United Nations infantry divisions, with
additional support from the United States Air Force, against elements of the
15th and 12th Corps of the People's Republic... 阅读全帖 |
|
e****e
发帖数: 2010 | 46 方法比较傻瓜，需要把Code编辑好，安装浏览器Extension。 职业高中，技校的，文科
女生可以试试，大牛和名校毕业的就算了。觉得有用就给些包子，饿S了。
1) Download and Install iMacros IE extension
http://download.cnet.com/iMacros-for-Internet-Explorer/3000-125
10586882.html
2) Create your code CVS txt file, PlumTarget.txt, Looks like:
Email,CardN,AccessN,RedeemN
xxx@xxx,xxxx,xxxx,xxxxxx
xxx@xxx,xxxx,xxxx,xxxxxx
…...................
Note: 四列文件，只有逗号，没有任何空格。可以用Excel, 存为cvs text file.
3) Edit and Play iMacros File
Edit #Current.iim file as following, save as any file n... 阅读全帖 |
|
|
d**e
发帖数: 6098 | 48 create table thirdTable
as
select t1.col1, t2.col2
from table1 t1, table2 t2; |
|
u*****o
发帖数: 1224 | 49 最近在自学，想请教大家一个renaming的问题
用as可以实现两种renaming, rename一个column, 或者rename一个table
第一种：就是说比如我的table有个column A, 我把A的每个数×10000/2.5（想象一个
特别复杂的数学式，太复杂了以至于我必须rename成B）
然后我想select B-A>1的row
那我必须这么写：select A, A*1000/2.5 as B from...where A*1000/2.5 - A > 1
想用B代替calculated expression (select A, A*1000/2.5*800+6 as B from where B
-A>1) 就不work!!
第二种情况是rename一个table,
select T1.col1, T2.col2
from table1 as T1, table2 as T2 where...
这里rename table的话，我就可以用简写T1,T2出现在表达式里，甚至出现在renaming
的前面。。为什么第一个例子就不能用简写呢
汗，好难说清楚，不知有... 阅读全帖 |
|
|
