Natural rock often suffers from cyclic wetting–drying involving different water types, and the resulting deterioration may differ from laboratory tests using distilled water or salt solutions. An inappropriate estimation of this deterioration effect may lead to fatal geological hazards and engineering failures. A multiscale study is conducted to investigate the physical and mechanical features of sandstone in Three Gorges Reservoir region (TGR sandstone) subjected to cyclic wetting–drying of Yangtze River water. During this study, three types of water, i.e., Yangtze River water, ionized water having similar ion compositions as the Yangtze River water, and distilled water, are used for comparison. The results show that the multiscale physical properties including mineral compositions (especially calcite and albite), micro-pore parameters, computed tomography values, and macro-mechanical parameters (i.e., Young’s modulus, uniaxial compression strength and tensile strength) are remarkably altered during the cyclic wetting–drying process. Significant correlations are found between these numerous multiscale properties. The results indicate that changes of mineral compositions and microstructure are the primary reasons for the deterioration of sandstone strength. The deterioration effect of distilled water on TGR sandstone is the least, while the effect of ionized water is the greatest, and that of river water being intermediate. These differences are ascribed to different chemical interactions, together with possible microorganism effects for river water, as microorganisms in river water potentially weaken the deterioration of cyclic wetting–drying of river water. In situ water is recommended for studying how rock properties are affected by water–rock interactions in real settings.