beta

最新推荐文章于 2023-11-08 16:15:07 发布
「已注销」
最新推荐文章于 2023-11-08 16:15:07 发布
阅读量519
点赞数 1
本文链接：https://blog.csdn.net/eds124/article/details/88581519
版权
--"Data Source=.;Initial Catalog=master;Integrated Security=True"
--"Data Source=.;Initial Catalog=master;User ID=sa;Password=Abcdefg1"
--Private Shared ReadOnly cs As String = "Server=127.0.0.1;Database=mydata;User Id=root;Password=;Character Set=gbk"

drop table userinfo;
create table userinfo
(
	username nvarchar(10) not null default N'无',  
	userpass nvarchar(10) not null default N'无',
	a bit not null default 0,
	b bit not null default 0,
	c bit not null default 0,
	d bit not null default 0,
	e bit not null default 0,
	f bit not null default 0,
	g bit not null default 0,
	insertsmalldatetime smalldatetime not null default getdate(),
	primary key(username)
);

insert into userinfo values ('admin', 'admin', 1, 1, 1, 1, 1, 1, 1, getdate());
insert into userinfo (username, userpass) values (N'user', N'user');
insert into userinfo (username, userpass) values (N'张三', N'333');
insert into userinfo (username, userpass) values (N'李四', N'444');
insert into userinfo (username, userpass) values (N'555', N'a');
insert into userinfo (username, userpass) values (N'666', N'a');
insert into userinfo (username, userpass) values (N'df6', N'a');
insert into userinfo (username, userpass) values (N'6asd', N'a');
insert into userinfo (username, userpass) values (N'asdwerf6', N'a');
insert into userinfo (username, userpass) values (N'asjndf6', N'a');
insert into userinfo (username, userpass) values (N'df3g6', N'a');
insert into userinfo (username, userpass) values (N'dfg6', N'a');
insert into userinfo (username, userpass) values (N'6azsdf', N'a');
insert into userinfo (username, userpass) values (N'as4df6', N'a');
insert into userinfo (username, userpass) values (N'6dtyhfg', N'a');
insert into userinfo (username, userpass) values (N'dsq23fg6', N'a');
insert into userinfo (username, userpass) values (N'6ngdf', N'a');
insert into userinfo (username, userpass) values (N'6zdfg', N'a');
insert into userinfo (username, userpass) values (N'dfgn6', N'a');
insert into userinfo (username, userpass) values (N'6d3f', N'a');
insert into userinfo (userpass) values (N'a');
select * from userinfo 

drop table customer;
create table customer(
name varchar(12) not null default '无',
mobile varchar(50) not null default '无',
address varchar(50) not null default '无',
email varchar(20) not null default '无',
remark varchar(20) not null default '无',
intention varchar(20) not null default '无',
username varchar(20) not null default '无',
insertdate date not null default '1900-01-01',
primary key(name)
);

insert into customer values ('孙先生', '15154221111', '山东', 'a@163.com', '无', '没兴趣', '无', now());
insert into customer values ('李先生', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '无', now());
insert into customer values ('王先生', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '无', now());
insert into customer values ('刘先生', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '无', now());
insert into customer values ('赵先生', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '无', now());
insert into customer values ('李女士', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '无', now());
insert into customer values ('孙女士', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '无', now());
insert into customer values ('王科长', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '张三', now());
insert into customer values ('李局长', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '李四', now());
insert into customer values ('张处长', '15154221111', '山东', 'a@163.com', '无', '感兴趣', '李四', now());

select * from customer order by 客户 desc;
select * from customer where 跟进 = '';  -- 这是客户池
select * from customer where 跟进 = '张三';  -- 这是张三的客户


drop table userinfo;
create table userinfo(
username nvarchar(10) not null default N'无',
userpass nvarchar(10) not null default N'无',
a bit not null default 0,
b bit not null default 0,
c bit not null default 0,
d bit not null default 0,
e bit not null default 0,
f bit not null default 0,
g bit not null default 0,
h bit not null default 0,
i bit not null default 0,
j bit not null default 0,
k bit not null default 0,
l bit not null default 0,
m bit not null default 0,
n bit not null default 0,
o bit not null default 0,
p bit not null default 0,
q bit not null default 0,
r bit not null default 0,
s bit not null default 0,
t bit not null default 0,
u bit not null default 0,
v bit not null default 0,
w bit not null default 0,
x bit not null default 0,
y bit not null default 0,
z bit not null default 0,
insertsdm smalldatetime not null default getdate(),
primary key(username)
);

insert into userinfo values ('管理员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('admin','admin',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('root','root',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('张三','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('李四','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('王五','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('刘六','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('反对','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('sdffdgd','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('管sdf理员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('管sdf员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('sdf理员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('管f理员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('管d理员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('sdf员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('管d理c员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('管理dfg员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('sdfyu员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());
insert into userinfo values ('管理f员','123',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now());

select * from userinfo;




--商品表
drop table t_goods
create table t_goods(
	goodscode nvarchar(10) not null default N'默认编码', primary key(goodscode),
	goodsname nvarchar(10) not null default N'默认名称',
	fenlei nvarchar(10) not null default N'默认分类',	
	danwei nvarchar(10) not null default N'默认单位',
	danjia money not null default 0,
	insertdatetime2 datetime2 not null default getdate()
);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r001', N'猪肉', N'斤', 8);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r002', N'羊肉', N'斤', 32);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r003', N'牛肉', N'斤', 30);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r004', N'驴肉', N'斤', 30);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r005', N'狗肉', N'斤', 40);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r006', N'猫肉', N'斤', 20);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r007', N'兔肉', N'斤', 18);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r008', N'鸡肉', N'斤', 5);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r009', N'鸭肉', N'斤', 6);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'肉类', N'r010', N'鹅肉', N'斤', 10);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c001', N'菠菜', N'斤', 5.5);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c002', N'白菜', N'斤', 1.2);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c003', N'芹菜', N'斤', 1.5);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c004', N'油菜', N'斤', 2.2);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c005', N'韭菜', N'斤', 9.6);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c006', N'包菜', N'斤', 2.2);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c007', N'生菜', N'斤', 5.5);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c008', N'萝卜', N'斤', 1.5);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c009', N'茭瓜', N'斤', 2.5);
insert into t_goods (fenlei,goodscode,goodsname,danwei,danjia) values (N'菜类', N'c010', N'冬瓜', N'斤', 1.5);
select * from t_goods order by fenlei asc, goodscode asc




--临时叫货单列表
drop table t_shop
create table t_shop(
	goodscode nvarchar(10) not null default N'默认编码', 
	goodsname nvarchar(10) not null default N'默认名称',
	fenlei nvarchar(10) not null default N'默认分类',
	danwei nvarchar(10) not null default N'默认单位',
	danjia money not null default 0,	
	shuliang bigint not null default 0,
	username nvarchar(10) not null default N'默认用户名'
);

--叫货单列表
drop table t_jiaohuo
create table t_jiaohuo(
	jiaohuodanhao uniqueidentifier not null default newid(),
	goodscode nvarchar(10) not null default N'默认编码', 
	goodsname nvarchar(10) not null default N'默认名称',
	fenlei nvarchar(10) not null default N'默认分类',	
	danwei nvarchar(10) not null default N'默认单位',
	danjia money not null default 0,	
	shuliang int not null default 0,
	jiaohuodate date not null default getdate(),
	username nvarchar(10) not null default N'默认用户名',
);

select * from t_jiaohuo
select goodscode , goodsname, fenlei, SUM(shuliang) shuliangheji, danjia,jiaohuodate from t_jiaohuo group by goodscode,goodsname,fenlei,danjia,jiaohuodate
select jiaohuodanhao, count(*) as tiaoshu, sum(danjia * shuliang) as hejijine, username, jiaohuodate  from t_jiaohuo group by jiaohuodanhao ,jiaohuodate ,username
select jiaohuodanhao, count(*) as tiaoshu, sum(danjia * shuliang) as hejijine, username, jiaohuodate  from t_jiaohuo where username = @username and jiaohuodate between start and end group by jiaohuodanhao ,jiaohuodate ,username





create table shenqing
(
	createdate smalldatetime not null default getdate(),
	shenqingren nvarchar(10) not null default '无',
	title nvarchar(20) not null default '无',
	content nvarchar(50) not null default '无',
	jingli nvarchar(10) not null default '无',
	jinglipizhun bit not null default 0,
	zongjingli nvarchar(10) not null default '无',
	zongjinglipizhun bit not null default 0,	
);

insert into shenqing values (getdate(), '张三', '请假', '因身体不适应请假', '李四', 0, '王五', 0);
insert into shenqing values (getdate(), '张三', '请假', '因身体不适应请假', '李四', 0, '王五', 0);

select * from shenqing