sql数据匹配
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_admpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
B. sql实现两列数据的匹配
设表名为TABLENAME,列1为COL1,列2为COL2则这样求出所有第一列在第二列中存在的记录：
SELECT * FROM TABLENAME A WHERE COL1 IN(SELECT COL2 FROM TABLENAME)
===============================
SELECT * FROM TABLENAME A
WHERE EXISTS(SELECT 1 FROM TABLENAME B WHERE B.COL2 LIKE '%'+A.COL1+'%')
=========================
SELECT DISTINCT A.* FROM TABLENAME A,TABLENAME B WHERE B.COL2 LIKE '%'+A.COL1+'%'
C. sql中怎么才能实现两张表 数据自动匹配
select * from 表1 a ，表2 b
where a.id=b.id
D. sql 查询在一张表中根据条件匹配另外一张表的字段
select t1.ID,member_name,group,date--等值连接
from t1,t2
where t1.ID=t2.ID
E. 求问SQL数据库中匹配语句怎么写
select regexp_replace('<?xml>','[<^>$]','') from al
结果：?xml
F. 怎么用sql语句,匹配多行数据的同一个字段
什么意思。。。？
select mainId
from mainTable
where mainId in (
select mainId from relationTable, tagTable
where relationTable.tagId = tagTable.tagId )
如果要特定的某个（某些）tagId，就增加条件
select mainId
from mainTable
where mainId in (
select mainId from relationTable, tagTable
where relationTable.tagId = tagTable.tagId
and tagTable.tagId = ?? ) //--或者 tagTable.tagId in (……)
G. sql匹配对应行更新数据
--假设tb1和tb2两个表，其tb1.id=tb2.id，将tb1.value写入tb2.value
selecta.*fromtb1ajointb2bonb.id=a.id
updatebsetb.value=a.valuefromtb2b
jointb1aona.id=b.id
H. sql 关于数据匹配
CREATE TABLE a (
name char(1),
nnum int,
time char(8)
);
CREATE TABLE b (
name char(1),
num int
);
INSERT INTO a
SELECT 'a', 3, '20111101' UNION ALL
SELECT 'a', 1, '20111102' UNION ALL
SELECT 'a', 2, '20111103' UNION ALL
SELECT 'b', 2, '20111104' UNION ALL
SELECT 'b', 3, '20111105' ;
INSERT INTO b
SELECT 'a', 3 UNION ALL
SELECT 'a', 2 UNION ALL
SELECT 'b', 3;
With MyCTE AS (
SELECT
ROW_NUMBER() over (partition by name order by time) as ID,
name,
nnum,
time
FROM
a
),
AllGoodsCTE AS (
SELECT
name,
SUM(num) as snum
FROM
b
GROUP BY name
)
SELECT
name,
nnum,
time,
(SELECT SUM(nnum) as xnum FROM MyCTE M2 WHERE M2.name = Main.name AND M2.ID <= Main.ID) AS 累计数量,
(SELECT snum FROM AllGoodsCTE M3 WHERE M3.name = Main.name)
- (SELECT SUM(nnum) as xnum FROM MyCTE M2 WHERE M2.name = Main.name AND M2.ID <= Main.ID) AS 剩余库存,
CASE WHEN
(SELECT SUM(nnum) as xnum FROM MyCTE M2 WHERE M2.name = Main.name AND M2.ID <= Main.ID)
> (SELECT snum FROM AllGoodsCTE M3 WHERE M3.name = Main.name) THEN
nnum + (SELECT snum FROM AllGoodsCTE M3 WHERE M3.name = Main.name)
- (SELECT SUM(nnum) as xnum FROM MyCTE M2 WHERE M2.name = Main.name AND M2.ID <= Main.ID)
ELSE
nnum
END AS 实际配货
FROM
MyCTE Main
name nnum time 累计数量 剩余库存 实际配货
---- ----------- -------- ----------- ----------- -----------
a 3 20111101 3 2 3
a 1 20111102 4 1 1
a 2 20111103 6 -1 1
b 2 20111104 2 1 2
b 3 20111105 5 -2 1
(5 行受影响)