sql

		SELECT TOP 1 Psn_Employee.ID,Psn_Employee.Name,Sex,Psn_Employee.Code, 
    PassWord,PowerBound,Psn_Group.Name AS GroupName,Psn_Branch.Name AS BranchName, 
    Psn_Group.GCode,Color,Psn_Group.ID As GroupID,Psn_Branch.ID As BranchID 
    FROM Psn_Employee INNER JOIN Psn_Group ON GroupID = Psn_Group.ID INNER JOIN  Psn_Branch ON BranchID = Psn_Branch.ID WHERE 
		Psn_Employee.Code='DEA0001' 
		--Psn_Employee.Name = '李洪波'
		--Psn_Employee.Code = '200558'
		AND TmpLogin=1 ;
		
		
		--A纸张库存
		--材质
		SELECT *  from Mtr_Paper_Matter where  name like '%蘭色模造%';
		--新增纸张光栅
		Select * from Mtr_Paper_Category Where Matter = '蘭色模造';
		--查询库存
		SELECT *,Mtr_Paper_Category.Name As PaperName,Mtr_Paper_Category.Pound 
    FROM Mtr_Paper_Stock 
		INNER JOIN Mtr_Paper_Category ON Mtr_Paper_Stock.CategoryID = Mtr_Paper_Category.ID 
    WHERE Mtr_Paper_Stock.CategoryID =538 ORDER BY Mtr_Paper_Stock.Type, Mtr_Paper_Stock.Length; 
		
		
		--光栅材质
		Select ID,Name,FromPound,ToPound,Memo,StateInt from Mtr_Paper_Matter Where Raster=1 Order by Name ;
		--光栅品牌
		Select * from Mtr_Paper_Brand Order by Name;
		--海关类别
		Select * from Ctm_Customs_Category Where Type = 0 Order By Code,Name;
		--材质类别
		Select ID,Name,FromPound,ToPound,Memo,StateInt from Mtr_Paper_Matter Order by Name;
		
		--B料号模板
		--左侧列表
		Select * from Ptn_PartNo_Category Order By ID;
		--删除3D类
		DELETE from Ptn_PartNo_Category where ID = 9;
		--将纸品类改成礼品盒类
		UPDATE Ptn_PartNo_Category SET NAME = '禮品盒' where id = 8;--紙品
		
		--第二层
		SELECT Ptn_Model.ID,Ptn_Model.Name,Ptn_Model.CategoryID, 
      Ptn_PartNo_Category.Name AS Category , Ptn_Model.Memo,Ptn_Model.StateInt 
    FROM Ptn_Model INNER JOIN Ptn_PartNo_Category ON Ptn_Model.CategoryID = Ptn_PartNo_Category.ID  Where Ptn_Model.CategoryID =9 Order By 		Ptn_Model.Name;
		
		
		--C客户
		SELECT Psn_Group.* FROM Psn_Branch INNER JOIN Psn_Group ON Psn_Branch.ID=BranchID Where Psn_Branch.Name='業務部' Order by Psn_Group.Name;
		--部门
		SELECT * FROM Psn_Branch;
		--分组
		SELECT * from Psn_Group;
		--客户
		Select ID,ClientID,Name,ChineseName,Code,CorpType,Hide,StateInt,CreateDate From Clt_Client Where GroupID=1 Order by Name;
		--员工
		SELECT Psn_Employee.ID,Psn_Employee.Name,Psn_Employee.Sex,Psn_Employee.Code, 
    Psn_Group.Name AS [Group],Psn_Branch.Name AS Branch,Psn_Employee.StateInt, 
    Cls_ClassTime.Name AS ClassTimeName, Psn_Employee.RankID,Psn_Rank.Name AS Rank FROM Psn_Employee INNER JOIN Psn_Group 
		ON Psn_Employee.GroupID = Psn_Group.ID INNER     JOIN Psn_Branch ON Psn_Group.BranchID = Psn_Branch.ID INNER JOIN Psn_Rank
		ON      Psn_Employee.RankID = Psn_Rank.ID LEFT OUTER JOIN Cls_ClassTime ON              
		Psn_Employee.ClassTimeID = Cls_ClassTime.ID  WHERE Psn_Employee.GroupID =8 AND Psn_Employee.StateInt < 2 ORDER BY  Psn_Employee.Name  ;
		
		--部门
		Select * From Psn_Branch Order By Sort; 

--查询用户拥有的角色
		SELECT Psn_Role.ID,Psn_Role.Name,BranchID,Psn_Role.Memo,Psn_Role.StateInt FROM Psn_Employee_Role INNER JOIN Psn_Role ON RoleID =Psn_Role.ID Where EmployeeID=20
		
		--查询用户拥有的权限
		SELECT Distinct Psn_Power.ID,Psn_Power.Name,TypeID,Psn_Power.StateInt 
    FROM Psn_Employee_Role INNER JOIN Psn_Role_Power ON Psn_Employee_Role.RoleID =
    Psn_Role_Power.RoleID INNER JOIN Psn_Power ON PowerID = Psn_Power.ID 
    WHERE EmployeeID = 20 ORDER BY Psn_Power.Name 
		
		--员工表
		SELECT * FROM Psn_Employee;
		--角色-资源表
		SELECT * FROM Psn_Role_Power;
		--员工-角色关系表
		SELECT * FROM Psn_Employee_Role;
		--角色
		SELECT * FROM Psn_Role ;	


	--根据分组ID查询客户
		Select ID,ClientID,Name,ChineseName,Code,Hide,StateInt,CreateDate,CorpType From Clt_Client Where GroupID=1 Order by Name;
		
		
		--根据客户查询料号
		SELECT Ptn_PartNo.ID,Ptn_PartNo.Name,Ptn_PartNo.PNCode,Ptn_PartNo.Spec, 
      Ptn_PartNo.Category,Ptn_PartNo.Hide,Ptn_PartNo.Mother,Ptn_PartNo.InitClientID, 
      Ptn_PartNo.OutSource,Ptn_PartNo.CreateDate,Ptn_PartNo.StateInt,Ptn_PartNo.State, 
     Ptn_PartNo.FontColor,Ptn_PartNo.FontBold,Ptn_PartNo.FontLock,Ptn_PartNo.InfoMemo,
      Ptn_Stock.OutDate,Ptn_PartNo.Account 
    FROM Ptn_Client_Map INNER JOIN Ptn_PartNo ON Ptn_Client_Map.PartNoID =   Ptn_PartNo.ID LEFT OUTER JOIN Ptn_Stock ON Ptn_PartNo.ID = Ptn_Stock.PartNoID WHERE ClientID=2 And Ptn_PartNo.Hide=0 Order By Name 


select * from  Mtr_Paper_Stock 
INNER JOIN Mtr_Paper_Category ON Mtr_Paper_Stock.CategoryID = 882;


select * from  Odr_Order 
select * from  Clt_Client_Detail;

Select * From Acc_TaxRate Order By TaxType,Rate,Name ;
Select ID,Name,Rate,R100Unit,RenovatorID,LastDate,StrName,Symbol From Acc_Money_Rate ;

SELECT * FROM Odr_Order_Detail ;

SELECT * FROM Odr_Order_OtherFee;

Select * from Acc_Connection Order By Name ;

select ClientID from  Ptn_Client_Map INNER JOIN Ptn_PartNo ON Ptn_Client_Map.PartNoID =   Ptn_PartNo.ID LEFT OUTER JOIN Ptn_Stock ON Ptn_PartNo.ID = Ptn_Stock.PartNoID ;

select OrderID from  Odr_Order_Detail INNER JOIN Ptn_PartNo ON PartNoID = Ptn_PartNo.ID;

		

 

转载于:https://my.oschina.net/u/3424406/blog/1798675