1.如何刪除表中的重復記錄？(這裡指記錄的每個字段都要相同)
select distinct * into #temp from tab
delete tab
insert tab select * from #temp
drop table #temp
1.DISTINCT 是 SUM、AVG 和 COUNT 的可選關鍵字。如果使用 DISTINCT，那麼在計算總和、平均值或計數之前，先消除重復的值。
如果使用 DISTINCT 關鍵字，表達式必須只包含列名。而不能包含算術表達式。
以下查詢返回商務書籍的平均價格（不包括重復的值）：
USE pubs
SELECT AVG(DISTINCT price)
FROM titles
WHERE type = 'business'
2.DISTINCT 關鍵字可從 SELECT 語句的結果中除去重復的行,distinct 後面的字段可以是多個或*,是一個那就各軍兵種那個字段來取不重復的,
如果是多個,那就是篩選所選的字短都相同的記錄.
USE pubs
SELECT DISTINCT au_id--按照一個字段篩選
FROM titleauthor
USE pubs
SELECT DISTINCT au_id,au_name --按照兩個字段篩選
FROM titleauthor
2.怎樣返回數據庫中用戶表的表單名
select name from sysobjects where xtype='U'
select name from sysobjects where xtype = 'u' and status >=0
3.
http://community.csdn.Net/Expert/topic/4191/4191899.XML?temp=.5814325
各位大大請幫個忙,
一個表中A字段是int型的自動編號,B字段是首先要獲取A字段已有的自動編號數據再經過加入時間等後生成的數據,表如下
C,D(日期),E為其他數據
列名 A(自動遞加) B(A字段數據+日期等) C D E
---------------------------------------------------
1 A+D . . .
2 A+D . . .
---- 建立測試環境：
create table table1(a int identity,b varchar(20),c datetime,d datetime,e int)
create proc proc1
@c datetime,
@d datetime,
@e int
as
declare @f int
insert table1 (c,d,e) values(@c,@d,@e)
select @f=@@identity
if @@error=0
begin
update table1 set b=convert(varchar,a)+convert(varchar(12),d,120) where a=@f
end
---執行存儲過程
exec proc1 '2001-10-01','2001-10-20',45
select * from table1
4.事務問題
http://community.csdn.Net/Expert/topic/4245/4245634.XML?temp=.663891
(1)try：
-------------------------------------------------------------------------
CREATE PROCEDURE sp_Order_UpdateOrderFormHeadByAffirm
@OrderFormHeadID int,
@AffirmPerson nvarchar(50)
AS
BEGIN TRANSACTION
DECLARE @OrderFormNo nvarchar(50), @FranchiserNo nvarchar(10), @TotalSum decimal(18,4)
--更新狀態為確認
UPDATE
AD_U_HEAD_A_SSGL
SET
Tag = 1
WHERE
OrderFormHeadID = @OrderFormHeadID
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------
--返回 訂單管理（HEAD）的一些信息
SELECT
@OrderFormNo = OrderFormNo,
@FranchiserNo = FranchiserNo,
@TotalSum = TotalSum
FROM
AD_U_HEAD_A_SSGL
WHERE
OrderFormHeadID = @OrderFormHeadID
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------
--更新 訂單管理（DATA）
UPDATE
AD_U_DATA_A_SSGL
SET
Tag = 1,
AffirmPerson = @AffirmPerson,
AffirmDate = GETDATE()
WHERE
OrderFormNo = @OrderFormNo
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------
--經銷代理資信余額（MAIN）
EXEC sp_Order_UpdateCreditBalance @FranchiserNo, 0, 0, 0, @TotalSum, 0
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------
--新增 訂單確認日志（SLOG）
INSERT INTO
AD_U_SLOG_A_DDQR(OrderFormNo, GoodsNo, Quantity,
UnitPrice, ProductPackingNo,TotalQuantity, TotalSum, Rebate, FactSum)
SELECT
OrderFormNo, GoodsNo, Quantity, UnitPrice, ProductPackingNo,
TotalQuantity, TotalSum, Rebate, FactSum
FROM
AD_U_DATA_A_SSGL
WHERE
OrderFormNo = @OrderFormNo
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
GO
(2)----------------------------------
CREATE PROCEDURE sp_Order_UpdateOrderFormHeadByAffirm
@OrderFormHeadID int,
@AffirmPerson nvarchar(50)
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
......
(3)你說得沒錯 其實你那樣用事務的畫沒什麼作用，每個存儲過程都是一個事務。如果用事務最好有出錯的處理是否回滾之類的東西。但是要考慮好表之間的關聯性，如果都是一些單獨的表，可以分幾個事務處理，如果是父子表還是要放在一個事務裡面。保證其數據的准確
性。
4請給條如何找出重復記錄的SQL語句
select id,name,parentDeptid,status as state,type,showindex,url,corpid = 1001
from zfj_dept
日期:
select convert(varchar(16),getDate(),120) 2005-11-18 10:20
select convert(varchar,datepart(minute,getdate())) 獲得分鐘且轉換為字符型
內聯結/外聯結
--返回兩個表中共有的所有記錄
select *
from testTable as a
inner join TestTableChild as b on a.id = b.parentid
--返回(左表)TestTable所有記錄
select *
from testTable as a
left outer join TestTableChild as b on a.id = b.parentid
--返回(右表)TestTableChild的所有記錄
select *
from testTable as a
right outer join TestTableChild as b on a.id = b.parentid
--- 返回 兩個表裡共有的記錄,且不重復
select a.id,a.name,b.name
from testTable as a
inner join TestTableChild as b on a.id = b.parentid
group by a.id,a.name,b.name
--返回(左表)TestTable所有記錄
select a.id,a.name,b.name
from testTable as a
left outer join TestTableChild as b on a.id = b.parentid
group by a.id,a.name,b.name
--------
select a.id,a.subject,b.contentType,c.AuguryUp,c.AuguryDown,
case c.type when '1' then '愛情' when '2' then '財運' when '3' then '事業' end as type
from MMS_Content as a
left outer Join MMS_ContentChild as b on a.id = b.parentid
left outer join AuguryList as c on a.id = c.parentid
where a.dept = 6
group by a.id,a.subject,b.contentType,c.AuguryUp,c.augurydown,c.type
向一個表A中插入記錄,並且插入的記錄在A中不存在(通過一個字段來判斷)
insert into trace_users (tracekey,muteSMS,CreateTime,traceuser,tracetime,traceSlot,traceduration)
select 'TRACE_TIMER',0,getdate(),mobileid,getdate(),'30','0'
from Epm_EmployeeList where corpid = 10001
and mobileid not in (select traceuser from trace_users )
and mobileid like '13%' and len(mobileid) = 11
下面的要好些(not exists)
insert into trace_users (tracekey,muteSMS,CreateTime,traceuser,tracetime,traceSlot,traceduration) select 'TRACE_TIMER',0,getdate(),mobileid,getdate(),'30','0' from Epm_EmployeeList where corpid = 10001 and not exists (select traceuser from trace_users ) and mobileid like '13%' and len(mobileid) = 11
cast 和convert DateAdd和DateDiff
-- 調度設置的時候更新人員狀態 --EPM_EmployeeList 裡的active = 1 --trace_Timer 裡的active = 1 --trace_users 裡的traceduration 清0
ALTER proc up_SetSchedule --create proc up_SetSchedule @nCorpId int, @vchEmployeeIds varchar(8000), @TimeStart DateTime, @TimeEnd DateTime as declare @sql varchar(8000),@TraceSolt int SET XACT_ABORT ON --任何一部有問題是都會回滾事務 BEGIN TRANSACTION --開始事務 update EPM_EmployeeList set activeStatus = 0 where corpid = @nCorpId and activeStatus != 0 if (@vchEmployeeIds !='') begin set @sql ='update EPM_EmployeeList set activeStatus = 1 where corpid= '+convert(varchar(10),@nCorpId)+ ' and id in ('+@vchEmployeeIds+')' exec (@sql) --更新Trace_timer set @sql ='update trace_Timer set activeStatus = 1 where corpid= '+convert(varchar(10),@nCorpId)+ ' and mobileid in (' set @sql= @sql+'select mobileid from EPM_EmployeeList where id in ('+@vchEmployeeIds+'))' exec (@sql) select @TraceSolt = (select tracesolt from EPM_EnterpriseList where id = @nCorpId) ('+@vchEmployeeIds+'))' set @sql ='update trace_users set createTime ='''+cast(@timeStart as varchar)+'''' set @sql= @sql+',traceTime='''+cast(DateAdd(minute,@TraceSolt,@timeStart) as varchar ) set @sql= @sql+''',traceDuration ='''+convert(varchar,datediff(minute,@timeStart,@timeEnd))+''' where traceuser in (' set @sql= @sql+'select mobileid from EPM_EmployeeList where id in ('+@vchEmployeeIds+'))' exec (@sql) end COMMIT TRANSACTION --提交事務 GO --插入到臨時表裡
select distinct (a.id),a.corpname,a.corplinkman,a.phonenumber,a.createtime,a.address
,(select distinct d.name
from
dz_subinfoDefine as c,
dz_mainInfoDefine as d
where c.maintype = d.maintype
and c.subtype = b.infotype) as type
into #table2
from dz_corporation as a
left join dz_information as b on a.id = b.corpid
--插入到表裡.需要兩次是因為無法對類別(大類)進行排序
select case when type IS NULL then '未知類別' else type end as 大類別,corpname as 名稱 ,corplinkman as 聯系人,phonenumber as 聯系電話,address as 地址 ,createTime as 創建時間 into Table1 from #table2
order by type
----刪除臨時表
drop table #table2
好的方法????找不到........可能是數據庫設計的不好.
通過另一個表來更新本表的記錄.
begin transaction
update EPM_Employeelist set loginname =b.loginname,password= b.passWord
from zfj_users as b
where
EPM_Employeelist.userid = b.userid
and corpid = 10001
rollback transaction
在in子句中如何寫變量的表達式問題
declare @ids varchar(8000) set @ids = '14501,14502,14503' select * from table1 where charindex(','+cast(id as varchar(20))+',',','+@ids+',')>0
一個存儲過程
-獲得系統對象:
sysobjects
在數據庫內創建的每個對象（約束、默認值、日志、規則、存儲過程等）在表中占一行。只有在 tempdb 內，每個臨時對象才在該表中占一行。
--所有procedure && 名稱='up_DeleteEnterprise'
select * from sysobjects where xtype = 'P' and name = 'up_DeleteEnterprise'
--所有Triger
select * from Sysobjects where xtype = 'TR'
--所有用戶Table
select * from Sysobjects where xtype = 'u'
xtype的值:
-- 調度設置的時候更新人員狀態 --EPM_EmployeeList 裡的active = 1 --trace_Timer 裡的active = 1 --trace_users 裡的traceduration 清0 --執行例子:exec up_SetSchedule 1001,'1009,1019' alter proc up_SetSchedule --create proc up_SetSchedule @nCorpId int, @vchEmployeeIds varchar(8000), @TimeStart DateTime, @TimeEnd DateTime as declare @sql varchar(8000),@TraceSolt int SET XACT_ABORT ON --任何一部有問題是都會回滾事務 BEGIN TRANSACTION --開始事務 --更新企業表中的起始時間 update EPM_EnterpriseList set ScheduleStart = @TimeStart,ScheduleEnd = @TimeEnd --更新員工調度狀態(清0) update EPM_EmployeeList set activeStatus = 0 where corpid = @nCorpId and activeStatus != 0 or activeStatus is null --Trace_Timer裡員工狀態清0 update Trace_Timer set activeStatus = 0 where corpid = @nCorpId and activeStatus != 0 or activeStatus is null --Trace_Users裡員工狀態清0 update Trace_Users set traceDuration = 0 where traceUser in (select mobileid from EPM_EmployeeList where corpid = @nCorpId) if (@vchEmployeeIds !='') begin set @sql ='update EPM_EmployeeList set activeStatus = 1 where corpid= '+convert(varchar(10),@nCorpId)+ ' and id in ('+@vchEmployeeIds+')' exec (@sql) --更新Trace_timer 執法局不需要對次表操作. --set @sql ='update trace_Timer set activeStatus = 1 where corpid= '+convert(varchar(10),@nCorpId)+ ' and mobileid in (' --set @sql= @sql+'select mobileid from EPM_EmployeeList where id in ('+@vchEmployeeIds+'))' --exec (@sql) select @TraceSolt = (select tracesolt from EPM_EnterpriseList where id = @nCorpId) --更新Trace_users 有與沒有corpid所以只能根據手機號了. --set @sql ='update trace_users set traceDuration =0 where traceuser in (' --set @sql= @sql+'select mobileid from EPM_EmployeeList where id in ('+@vchEmployeeIds+'))' --先判斷用戶是否在Trace_Users裡存在,如果存在則修改它,如果不存在需要添加一條記錄. --可以先增減沒有的記錄,然後統一更新這些記錄. --增加 exec up_SetSchedule_AddUsers @vchEmployeeIds --存儲過程裡執行存儲過程. --更新 set @sql ='update trace_users set createTime ='''+cast(@timeStart as varchar)+'''' set @sql= @sql+',traceTime='''+cast(DateAdd(minute,-@TraceSolt,@timeStart) as varchar ) set @sql= @sql+''',traceDuration ='''+convert(varchar,datediff(minute,@timeStart,@timeEnd))+''' where traceuser in (' set @sql= @sql+'select mobileid from EPM_EmployeeList where id in ('+@vchEmployeeIds+'))' exec (@sql) COMMIT TRANSACTION --提交事務 GO --增加表裡不存在的記錄.傳遞的參數為員工ID的集合(號碼間用逗號分開),判斷是否存在的方法,與上個相比效率高 create procedure up_SetSchedule_AddUsers @vchEmployees varchar(8000) = '' as --Declare @vchEmployees varchar(8000) --set @vchEmployees = '1172,1229,1271' --Set @vchEmployees = ''''+replace(@vchEmployees,',',''',''')+'''' --select @vchEmployees declare @vchMobiles varchar(8000) --手機號碼集合 --set @vchEmployees = '1172,1229,1271' SET XACT_ABORT ON --一步出現問題則全部回滾 Begin Transaction --獲得手機號碼集合 set @vchMobiles = '' select @vchMobiles = @vchMobiles+','+cast(mobileid as varchar(20)) from EPM_EmployeeList where charindex(','+cast(id as varchar(20))+',',','+@vchEmployees+',')>0 set @vchMobiles=stuff(@vchMobiles,1,1,'') --select @vchMobiles while charindex(',',@vchMobiles)>0 begin insert into Trace_Users(TraceKey,MuteSMS,CreateTime,TraceUser,TraceTime,TraceSlot,TraceDuration) select 'TRACE_TIMER',0,getdate(),left(@vchMobiles,charindex(',',@vchMobiles)-1),getDate(),30,0 where not exists(select traceUser from Trace_Users where traceUser=left(@vchMobiles,charindex(',',@vchMobiles)-1)) set @vchMobiles = stuff(@vchMobiles,1,charindex(',',@vchMobiles),'') end insert into Trace_Users(TraceKey,MuteSMS,CreateTime,TraceUser,TraceTime,TraceSlot,TraceDuration) select 'TRACE_TIMER',0,getdate(),@vchMobiles,getDate(),30,0 where not exists(select 1 from Trace_Users where TraceUser=@vchMobiles) ---判斷是否已經有此記錄. --rollback Transaction Commit Transaction go up_SetSchedule_AddUsers '1172,1229,1271' exec up_SetSchedule @nCorpId = N'10001', @vchEmployeeIds = N'1271,1484', @TimeStart = N'2005-11-25 8:30:00', @TimeEnd = N'2005-11-25 17:30:00' ---取消一人或多人的調度,多人之間用逗號分開 create procedure up_Schedule_Cancel @vchEmployees varchar(8000) as declare @vchMobiles varchar(8000) --declare @vchEmployees varchar(8000) --set @vchEmployees = '1484' --獲得手機號碼集合 set @vchMobiles = '' select @vchMobiles = @vchMobiles+','+cast(mobileid as varchar(20)) from EPM_EmployeeList where charindex(','+cast(id as varchar(20))+',',','+@vchEmployees+',')>0 set @vchMobiles=stuff(@vchMobiles,1,1,'') set xact_abort on begin transaction update EPM_EmployeeList set activestatus = 0 where charindex(','+cast(mobileid as varchar(20))+',',','+@vchMobiles+',')>0 update Trace_Users set traceduration = 0 where charindex(','+cast(traceUser as varchar(20))+',',','+@vchMobiles+',')>0 commit transaction go
C = CHECK 約束
D = 默認值或 DEFAULT 約束
F = FOREIGN KEY 約束
L = 日志
FN = 標量函數
IF = 內嵌表函數
P = 存儲過程
PK = PRIMARY KEY 約束（類型是 K）
RF = 復制篩選存儲過程
S = 系統表
TF = 表函數
TR = 觸發器
U = 用戶表
UQ = UNIQUE 約束（類型是 K）
V = 視圖
X = 擴展存儲過程
---判斷臨時表是否存在
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#表名') and xtype='U')
drop table #表名
--字段值為NULL轉化為0,多看系統方法
isnull(字段,0)
--table1和talbe2交叉聯接的結果集再和table3左聯接
select a.*,c.others from
(select a.id,a.name,b.remark
from table1 a,table2 b) a
left join table3 c on a.id = c.parentid
自定義方法的使用.
--根據手機型號獲得其所屬模式.若模式為空或NULL則返回2(中模式)
--例子:select mms.dbo.uf_GetMobileModel(205) as aaa
alter FUNCTION uf_GetMobileModel
(@nModelId int)
RETURNS int
AS
BEGIN
--declare @nModelId int
--set @nModelId = 205
declare @Mode int
select @Mode= model from MMS_MobileChild where id = @nModelId
if @Mode = '' or @Mode is null
set @Mode = 2
--select @Mode
return (@Mode)
END
隨機數自定義方法
--通過VIEw來獲得隨即數字.在方法裡直接使用rand()不可以的.
create vIEw uv_GetRandom
as
select rand() as RandomValue
go
--生成n位隨機數的方法
--select locationServiceNew.dbo.getRandom(10)
--比較郁悶..好麻煩
alter function GetRandom(@nLength int=4)
returns int
as
begin
declare @nStart int,@vchLength varchar(50)
set @nstart = 1
set @vchLength = '1'
if @nLength >9 set @nLength = 9 --如果隨機數位數大於9那麼將其修改為9
if @nLength <1 set @nLength = 1 --如果隨機數位數小於1那麼將其修改為1
while @nStart <= @nLength
begin
set @vchLength = @vchLength+'0'
set @nStart = @nStart+1
end
declare @floatNum float,@intNum int
--set @num=cast(left(rand(),8) as float)*1000000
select @floatNum = RandomValue from uv_GetRandom
set @intNum = cast(@floatNum*cast(@vchLength as int) as int)
return (@intNum)
end
go
--測試
select locationServiceNew.dbo.getRandom(7)
用戶定義函數中不允許使用會對每個調用返回不同數據的內置函數
其中就有getdate哦
用戶定義函數中不允許使用以下內置函數：
@@CONNECTIONS
@@PACK_SENT
GETDATE
@@CPU_BUSY
@@PACKET_ERRORS
GetUTCDate
@@IDLE
@@TIMETICKS
NEWID
@@IO_BUSY
@@TOTAL_ERRORS
RAND
@@MAX_CONNECTIONS
@@TOTAL_READ
TEXTPTR
@@PACK_RECEIVED
@@TOTAL_WRITE
--創建指定位的隨即數
create proc up_GetRandom
@nLength int = 4, --隨即數的位數.
@vchValue int output
as
declare @nStart int,@vchLength varchar(50)
set @nstart = 1
set @vchLength = '1'
if @nLength >9 set @nLength = 9 --如果隨機數位數大於9那麼將其修改為9
if @nLength <1 set @nLength = 1 --如果隨機數位數小於1那麼將其修改為1
while @nStart <= @nLength
begin
set @vchLength = @vchLength+'0'
set @nStart = @nStart+1
end
set @vchValue= cast(rand()*cast(@vchLength as int) as int)
go
--測試
declare @randomValue int
Exec up_getRandom 8,@randomValue output
select @randomValue
按照月統計
select datepart(month,createtime) as '月分',count(mobileid) as '數量'
from User_answer
where createtime >= '2005-4-29' and Accessnumber = 1111111
group by datepart(month,createtime)
--按照月分統計,考慮多年
select * from
(
select convert(char(7),createtime,120) as 年月,count(*) as 數量 from users group by convert(char(7),createtime,120)
) as a
order by left(年月,4),right(年月,2)
-------------------------------------------------------------
/*
表TABLE1 ID（INT），CORPID（INT），CREATETIME（DATETIME）
CORPID 有重復的記錄。
現在想按照CREATETIME倒序取出CORPID不重復的前10條記錄（重復的只取1條）。
*/
create table TABLE1 (ID int identity(1,1),CORPID int,CREATETIME datetime)
insert table1(corpid,createtime)
select 11,'2006-02-09 14:21:48.357' union all
select 1 ,'2006-02-09 14:02:46.357' union all
select 1 ,'2006-02-09 14:03:46.357' union all
select 1 ,'2006-02-09 14:03:46.357' union all
select 10,'2006-02-09 14:04:46.357' union all
select 3 ,'2006-02-09 14:05:46.357' union all
select 5 ,'2006-02-09 14:05:46.357' union all
select 6 ,'2006-02-09 14:06:46.357' union all
select 7 ,'2006-02-09 14:07:46.357' union all
select 8 ,'2006-02-09 14:08:46.357' union all
select 9 ,'2006-02-09 14:09:46.357' union all
select 9 ,'2006-02-09 14:10:46.357' union all
select 10,'2006-02-09 14:11:46.357' union all
select 10,'2006-02-09 14:12:46.357' union all
select 10,'2006-02-09 14:13:46.357' union all
select 11,'2006-02-09 14:14:46.357' union all
select 11,'2006-02-09 14:15:46.357'
--方法一 可以獲得table中的所有字段/也可以只獲得一個.
select
TOP 10 a.*
from
TABLE1 a
where
not exists(select
1
from
TABLE1
where
CORPID=a.CORPID and (CREATETIME>a.CREATETIME or (CREATETIME=a.CREATETIME and ID>a.ID)))
order by
a.CREATETIME DESC
其它
select a.fee_user as '號碼',b.message as '內容',a.sendTime as '時間'
into test
from his_smdr a
left join his_deliver b on a.fee_User = b.src_userid
where
a.src_addr = '05555001' and a.sendtime >='2005-4-29'
and a.src_addr=b.dst_userid --需要
and datediff(ss,b.createtime,a.sendtime)<=10 and datediff(ss,b.createtime,a.sendtime)>='0'
order by sendTime desc
表 A ，有2個字段 Id--Int，Name--Varchar(20)
假設 表 A 裡存儲了30萬記錄，其中有1條記錄的 ID 是重復的，現在我想找出該條記錄ID，SQL語句？
select ID from A group by ID having count(1)>1
有一個觸發器,觸發該觸發器的方法有insert,update,delete
但是,我如何可以判斷我到底是用哪種方法觸發該觸發器
http://community.csdn.Net/Expert/topic/4234/4234894.XML?temp=.3572657
create trigger 觸發名 on 表名
instead of insert,update,delete
as
--insert插入
if not exists(select 1 from deleted)
begin
打印插入
end
--update更新
if exists(select 1 from inserted) and exists(select 1 from deleted)
begin
打印修改
end
--delete刪除
if not exists(select 1 from inserted)
begin
打印刪除
end
go
有兩個表 table1 和talbe2，字段和內容如下
字段 id name id so
00 n1 00 s1
01 n2 03 s3
03 n3
怎樣寫一個sql語句，得到記錄集
字段 id name so
00 n1 s1
01 n2
03 n3 s3
select A.*
,isnull(B.so,'') as 'so'
from table1 A
left join table2 B on A.id=B.id
獲得所有觸發器及其表名
select object_name(id) as 觸發器名稱, object_name(parent_obj) as 表名稱 from sysobjects
where xtype=N'TR'
-- 由一個表向另一表插入數據.
insert into EPM_DepartmentList(id,name,parentdeptid,state,type,showindex,url,corpid)
