S*******s
发帖数: 13043 | 1 if I have a table like this
CREATE TABLE myTable(
id integer,
category char(10),
value float
)
and want to get a cross table pivoting the category. a usual approach using
subquery and outter join is like following
SELECT myTable.id, t1.value as c1, t2.value as c2, t3.value as c3
FROM myTable
LEFT JOIN ( SELECT value FROM myTable WHERE category = 'category 1' ) c1 ON
myTable.id=c1.id
LEFT JOIN ( SELECT value FROM myTable WHERE category = 'category 2' ) c2 ON
myTable.id=c1.id
LEFT JOIN ( SELECT va... 阅读全帖 |
|
i*****w
发帖数: 75 | 2 Not sure if this is what you wanted, check the following query.
If you want to include future category names, then you need to use dynamic
SQL to get the distinct values in the category name column and then it will
pivot for you when you have new category name. For example, if you have a
new record with Category 4 and then Category 4 will be added to the result
grid.
declare @myTable table(id integer,category char(10),value float)
insert into @myTable values(1, 'category 1', 128)
insert into @my... 阅读全帖 |
|
s****e
发帖数: 282 | 3 Thanks.
How about the following two indexes:
1.
CREATE UNIQUE INDEX sid ON mytable (StudentID, ClassName)
This is for search like: SELECT * WHERE mytable.StudentID==100
2.
CREATE UNIQUE INDEX class ON mytable (ClassName, StudentID)
This is for search like: SELECT * WHERE mytable.ClassName==class2 |
|
w****g
发帖数: 1 | 4 in Oracle, try this one.
declare
isThere varchar2(1);
begin
select 'Y' into isThere
from user_tables
where table_name = 'MYTABLE';
if isThere = 'Y' then
truncate table MYTABLE;
else
create table MYTABLE (....);
end if;
end; |
|
i*****9
发帖数: 293 | 5 有下面这四个表和下面这几个要求，怎么写比较好能得到我想要的结果? 多谢
1，先选出USERACC_FOR_DOM_REP的所有的值：username, account_status, target_db.
2，再从上面的结果中除掉所有EXCEPTION_ACC的username(条件是EXCEPTION_ACC里的
username和target_db是USERACC_FOR_DOM_REP的)。
3, 再从上面的结果中除掉所有MAX_USER_LOGIN的username(条件是MAX_USER_LOGIN里的
username和target_db是USERACC_FOR_DOM_REP的，外加LAST_LOGIN < sysdate -90)。
4, 再从上面的结果中除掉所有MYTABLE的username(条件是MYTABLE里的username和
target_db是USERACC_FOR_DOM_REP的
SQL> desc USERACC_FOR_DOM_REP
Name Null? Type
... 阅读全帖 |
|
l****h
发帖数: 272 | 6 First, you can create an index for each row
Then you can run the following sql statement to get the number you want.
select a1.number from mytable a1, mytable a2 where a1.index = a2.index + 1
and a1.number <> a2.number
I guess this might be a way to combine the two steps. |
|
b*******r
发帖数: 6 | 7 土问题:
INSERT INTO MyTable (PriKey, Description) VALUES (user_input_key,
user_input_description)
被写成了:
INSERT INTO MyTable (PriKey, Description) VALUES
('user_input_key','user_input_description')
结果居然还是work,但就是如果user输入了,for example,"mark's",就会报错:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression "mark's', '".
WHY?Thanks |
|
f*******f
发帖数: 12 | 8 What I want to do is something like that:
declare @id
select @id=max(id) from mytable
if(@id is null)
@id=0
else
@id=@id+1
insert into mytable(@id,'aa','bbb')
But How to synchronized it when multiple connetions access database.
I use JDBC..
Thank you |
|
x***n
发帖数: 39 | 9 Suppose the primary key you wanna set is MyTable.ColumnA
Then
SELECT MAX(Duplicates), ColumnA
FROM
( SELECT COUNT(*) AS Duplicates, ColumnA
FROM MyTable
GROUP BY ColumnA); |
|
aw
发帖数: 127 | 10 use a self-join.
SELECT T1.lastname + isnull(T2.lastname,'')
FROM MYTABLE T1 LEFT JOIN MYTABLE T2
ON T1.firstname = T2.firstname AND T1.id < T2.id |
|
s****e
发帖数: 282 | 11 If I create index only on the first column, like:
CREATE INDEX sid ON mytable (StudentID)
The index will not be an unique index. The following query will return many
rows, right?
SELECT StudentID, ClassName WHERE mytable.Student ID = 100 |
|
z********y
发帖数: 14 | 12 try this one
Create function [dbo].[myFun]
(@P1 int)
returns varchar(1000)
as
begin
declare @aa varchar(1000);
set @aa ='';
select @aa = [value] + ',' + @aa from myTable
where id = @P1 order by [value] desc;
return @aa;
end
select top 1 [id] , [dbo].[myFun]([id]) from myTable where [id] = 1; |
|
o**********a
发帖数: 330 | 13 select *
from mytable
where mydate > sysdate-3
现在的问题是, 这个query是要过去三天的data. 我需要改进成要过去三个business
day的data. for example
如果今天是星期一
我的query应该是
select *
from mytable
where mydate > sysdate-5
问题是我如何能知道今天是星期几?
多谢 |
|
y****9
发帖数: 144 | 14 In Oracle 11g
SQL>select ID, category, value
2 from myTable;
ID CATEGORY VALUE
---------- -------------------- ----------
1 category 1 128
2 category 1 28
3 category 2 18
4 category 3 328
5 category 2 1
6 category 2 5
6 category 2 15
6 category 2 ... 阅读全帖 |
|
d****u
发帖数: 275 | 15 各位大侠，我需要将一堆xml数据导入mysql的数据库中，
xml文件名001，输入的表是mytable，xml文件以rootTag开始，
运行的情况是，我的表里边相应的column都更新了，但是没有任何数值；
请问这段script到底哪里出错了？？非常感谢！！
LOAD DATA LOCAL INFILE 'H:\\XXX\\2011\\001.xml'
INTO TABLE mydatabase.mytable
LINES STARTING BY '' TERMINATED BY ''
(@award)
SET
AwardID = ExtractValue(@award, 'AwardID'),
FirstName = ExtractValue(@award, 'FirstName'),
LastName = ExtractValue(@award, 'LastName'),
EmailAddress= ExtractValue(@award, 'EmailAddress'); |
|
c*****t
发帖数: 1879 | 16 我在弄 code generation 。可能是 xml / plain text / c 等输出。
所以弄个文件比如
...declarations..
myTable = { $array$ };
...rest
就能方便一些。不用把 declaration / myTable = 之类的全放进 Java 里。
算了。我还是用 Velocity 得了。有一点我不喜欢 velocity 的就是太多的
scripting 。但是刚发现几个严重的 StringTemplate 的问题。很多
bug/feature 。比如 int[][] array
$array:{r|$r;separator=","$|};separator="\n"$
不工作。里面 StringTemplate 干脆就没认出 $r$ 是 int[]。这个很讨厌，
不需要它自动识别 int[] 的时候它不自动，需要的时候它偏偏又不了。。。
奇怪，难道用 StringTemplate 的人从来不输出 hexadecimal 和输入
multi-dimensional array ？Google 了半天没结果 :( |
|
c******n
发帖数: 4965 | 17 this is what doesn't work for me:
I do a
select * from mytable;
then there are many columns, so I need to do
set lin 200
so they do not wrap around
then when I do a describe mytable,
the result has only 2 columns, but now they are shown really far apart; what
's worse, if I set lin=20000 to disable any line wrapping, (I can do this
because I could spool the output to a file, and later read in vim, with
nowrap option), these 2 columns are displayed in a completely messed up way |
|
L*******s
发帖数: 63 | 18 编译器好像是 g++ 4.3.4
struct eqstr
{
inline bool operator()(const char* s1, const char* s2) const
{
return 0 == strcmp(s1, s2);
}
};
typedef __gnu_cxx::hash_map
>, eqstr> MyTable;
现在有
MyTable t;
中途进行一系列添加操作
每隔一段时间进行以下输出
std::cout << t.size() << std::endl;
std::for_each(t.begin(), t.end(), display1);
display1如下：
void display1(std::pair pr)
{
std::cout << (pr.second ? "T " : "F ") << pr.first << std::endl;
}
我现 |
|
a**y
发帖数: 335 | 19 哦？这应该怎么做？
$conn=odbc_connect("odbcname","user","pwd");
$sql="Select some_nvarchar from myTable where id=286";
$result=odbc_exec($conn,$sql);
$row=odbc_fetch_array($result); |
|
t**i
发帖数: 688 | 20 If you include both username and password in your SQL query statement to
verify the logging event such as SELECT * FROM mytable WHERE username =
myname AND password = mypwd blah blah, then SQL injection may occur as long
as the malicious user type into the password field sth like "junk OR 3".
Use username only to retrieve the record (assumign username is unique) and
compare the retrieved password with HTML form posted password (after both
encrypted, of course, for better security).
You may als |
|
T*C
发帖数: 3040 | 21 我在sqlserver的存储过程中生成了一条语句
@sql_string='create table mytable (......)'
请问怎么执行? |
|
a**s
发帖数: 177 | 22
奇怪？！在存储过程中为什么不直接写create table mytable (......)？
在PB中是这样写的：
EXECUTE IMMEDIATE :sql_string |
|
s*****c
发帖数: 36 | 23 Hi, everyone:
本贴讨论SELECT TOP N问题.
现在正在一家计算机公司兼职,开发ASP应用. 在最近的一个商业
项目里有这样一个需求:用户在查询时,只需要按照某(些)列排序后TO
P 20的纪录. SQL SERVER 7很好地支持了TOP N 操作,但考虑到系统
移植问题, 我又考虑在其他几个主流DBMSs中如何实现. 鉴于只有DB2
UDB 7,ORACLE 8i,SQL SERVER 7,本贴仅讨论这三个DBMS.
简单地说,TOP N问题就是:在SELECT中,仅选择按照某(些)列排序
后TOP N的纪录. 考虑到等值问题,又可以分为两种: 一是仅仅返回N
条纪录(M 1), 二是还包括所有于第N条等值的纪录(M 2). 当然最内
层的子查询也可以有其他的子句, 或者TOP N也可以应用在没有ORDER
BY的情况下,这样更简单.
1. SQL SERVER 7: 用 TOP N (WITH TIES)
M1:
SELECT TOP N * FROM MYTABLE ORDER BY ORD_COL;
M2:
SELECT TOP N |
|
f*******h
发帖数: 1269 | 24 When I:
INSERT INTO mytable(name, desc) VALUES('Test Name', 'It's a good test')
this will cause problem, because ' after It.
How could I do? |
|
p*****e
发帖数: 58 | 25
INSERT INTO mytable(name, desc) VALUES('Test Name', 'It''s a
good test')
use '' (two single quote) for '. SQL uses ' as the escape
charactor like the \ in C/C++, or " in VB. |
|
f*******e
发帖数: 4 | 26 要查询一个表的记录，要求把一个时间段内的数据取出来，用标准的sql语句该如何写？
例如要求把1997-7-14 14：45：00 到1998-7-14 14：45：00之间的数据取出来
select * from mytable where ???
其中日期的字段名为mydate，时间的字段名为mytime
谢谢了 |
|
B**z
发帖数: 153 | 27 I think you are trying to generate sequence for primary key for mytable
you can just declare the id column as identity. when you insert into
you just values('aa','bb') |
|
b*e
发帖数: 3845 | 28 maybe this would be better
select count(*) as dup, columnA from myTable group by ColumnA
having dup>1 |
|
b******r
发帖数: 1106 | 29 I have a stored procedure
ALTER PROC mysp
@ID int,
@CID int,
@Status varchar(10) = NULL
AS
UPDATE mytable
SET CID=@CID, Status = @Status,
WHERE ID = @ID
declare @ClosureFlag char(1)
set @ClosureFlag = '0'
if @Status = '2'
begin
--update myothertable
delete from myanothertable where CID = @CID
INSERT INTO mythirdtable (CID, 'something deleted')
VALUES (@CID, @message)
-- set the flag for C# code use
set @ClosureFlag = '1'
end
select @ClosureFlag as ClosureFlag
Most time it r |
|
y********o
发帖数: 2565 | 30 对，我将学校那个Oracle10g的tnsnames.ora中相关的一节如下搬到我的XE之tnsnames.
ora了。
Oracle10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle10g.my.univ.edu)(PORT = 1521)
)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Oracle10g.my.univ.edu)
)
)
然后我说：
SQL> create database link mylink connect to johndoe identified by wassupman
using 'Oracle10g';
Database link created.
SQL> desc mytable@mylink;
ERROR:
ORA-12170: TNS:Connect timeout occurred
我按这个 h |
|
t*****g
发帖数: 1275 | 31 SELECT t1.id_student, t1.id_class, t1.grade,
(SELECT count(*) + 1 from mytalbe t2 WHERE t2.id_class = t1.id_class and t2
.grade > t1.grade)
FROM mytable t1 order by t1.id_class, t1.grade desc; |
|
f**c
发帖数: 629 | 32 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:
+-----+-----+-----+
|
|
U*********r
发帖数: 68 | 33 我想创建一个表, 它的结构是这样的:
create table mytable(
myidfield mediumint unsigned not null,
s00 float not null,
s01 float not null,
s02 float not null,
s03 float not null,
...
...
s99 float not null,
primary key(myidfield));
我觉得从s00字段敲到s99字段好累, 请问有什么快捷的方法?
mysql 里好象没有用 for 循环来生成一长串有规律的字符串的功能.
这个表不能再规范化了, 就得这么宽才行. |
|
B*****g
发帖数: 34098 | 34 I think 1 is only good (compare to index(StudentID))for
SELECT StudentID, ClassName WHERE mytable.StudentID==100
But if you server is "strong", I think your design is good. |
|
n********6
发帖数: 1511 | 35 Yeah.
It's better to understand what are the most frequently used query.
If a lot of request for
SELECT StudentID, ClassName WHERE mytable.Student ID = 100
then it is good. |
|
s******s
发帖数: 508 | 36 select
CusipCol
, case when ValueCol is null and nextValue is not null then nextID else
IDCol end IDCol
, case when ValueCol is null and nextValue is not null then nextValue else
ValueCol end ValueCol
from
(
select CusipCol, IDCol, ValueCol
,lead(ValueCol) over (partition by CusipCol order by IDCol) NextValue
,lead(ValueCol) over (partition by CusipCol order by IDCol) NextID
,row_number() over (partition by CusipCol order by IDCol) rownum
from mytable
) x
where rownum = |
|
o**********a
发帖数: 330 | 37 select *
from mytable
where case when select to_char(sysdat,'day')='sunday' then mydate > sysdate-
5 else mydate > sysdate-3 end;
i tried this, but it doesn't work. Do you know what happened here |
|
b****o
发帖数: 55 | 38 【 以下文字转载自 Software 讨论区,原文如下 】
发信人: bolero (ripple), 信区: Software
标 题: sql question
发信站: Unknown Space - 未名空间 (Fri Mar 5 09:16:43 2004) WWW-POST
i wanted to create a table, but one field name is "local", and the system does
not allow me to do that in sql
"create table MyTable (id integer, local varchar)".
Anybody knows how to avoid the confliction? Thanks a lot!! |
|
w******t
发帖数: 1422 | 39 Hi xt,
I am interested in what you posted here (I suppose I should see sth weird
happen)
however, after testing, I did not notice anything wrong - something I ignored?
I used oracle9i DB, and 9ias(9.0.3)
table:
CREATE TABLE MYTABLE
(
FOO CHAR(32)
)
used jdbc to insert and retrieve (used PreparedStatement) |
|
d*r
发帖数: 238 | 40
Here is my program:
public class Test {
public Test() {
String sql =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testdb";
DriverManager.registerDriver(new SQLServerDriver());
Connection conn = DriverManager.createConnection(sql, "sa", "sa");
Statement stmt = conn.createStatement();
String query = "SELECT * FROM mytable";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
int i = rs.getInt("id");
|
|
xt
发帖数: 17532 | 41
近
光知道catch不行，还要知道擦屁股的重要性。比如说这样写就不对：
try{
Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement("Select * from myTable");
ResultSet rset = stmt.executeQuery();
...
}catch( SQLException e ) {
...
}
原因是在出了exception后你没有办法擦屁股。所以要把conn, stmt, rset全部在try{}外
面
declare,然后在catch后面写
fianally{
if( rset!=null）｛
try{
rset.close();
}catch (Exception e )｛
//ignore
}
}
...
}
把rset, stmt和conn全部关闭。用finally还有个大忌就是在finally里面绝对
不可以返回, 更不可以返回值。 |
|
k*a
发帖数: 42 | 42 代码如下，要求鼠标击时弹出显示那一列的name
| Name | Address |
|---|
| Peter | S... 阅读全帖 |
|
d****n
发帖数: 1637 | 43 1.ｓｅｒｖｅｒ 方面，每次用户ｓｕｂｍｉｔ时候，同时返回一个当前记录的指针。
这个指针用来跟踪哪１０个记录已经被显示了。作为一个ｐａｒａｍｅｔｅｒ 回到你
的ｐｅｒｌ ｓｃｒｉｐｔ中。
假设你用 ｐｅｒｌ＋ｍｙｓｑｌ
sub get_nth_record{
$nth=$_[0]; // keep user returned pointer
$stmt=$_[1]; // and more params
mysql="SELECT * from myTABLE where conditions limit nth,nth+10";
return mysql_results;
}
^好久不用ｐｅｒｌ了，惭愧
2.if you do it from client side, that will be better.
use javascript. save all the results (if not too many) into an array object.
keep a global value/pointer to save cur... 阅读全帖 |
|
k**f
发帖数: 372 | 44 \begin{table}
\centering
% your table staff such as tabular below will be center-adjusted
\caption{title} % latex will add Table and sequence number
\label{tbl:mytable} % for cross reference
\end{table} |
|
c******3
发帖数: 296 | 45 SELECT TOP 5 abs(value - MYVALUE), id FROM mytable
The table is only sorted once when the index on value column is created. |
|
l******9
发帖数: 579 | 46 SELECT TOP 5 abs(value - MYVALUE), id
FROM mytable
order by value -- we need this , right ?
Also, the default index sorted tree is ascending ? |
|
c***z
发帖数: 6348 | 47 stream R in HIVE with the TRANSFORM command
e.g.
hive> ADD FILE myscript.r;
hive> SELECT TRANSFORM(*) USING 'myscript.r' FROM mytable; |
|
m*****6
发帖数: 36 | 48 【 以下文字转载自 Quant 讨论区 】
发信人: mitbbs6 (Peter), 信区: Quant
标 题: 新手请教 SAS Oracle 连接问题
发信站: BBS 未名空间站 (Tue Jul 21 23:10:13 2015, 美东)
libname oralib oracle path=mydb user=user123 password=mypass schema=myschema;
proc sql;
select *
from oralib.mytable;
quit;
今天新换了机器 ，run以上code的时候不work了，没法连接oracle。
ERROR:The Oracle engine cannot be found.
ERROR: Error in the LIBNAME statement.
大家知不知道是怎么回事， 是不是 oracle client没有装好啊 ？
多谢 |
|
|
